Nabler is a digital analytics consulting company providing data infrastructure, Business Intelligence, Conversion rate optimization and data science services. You can trust us for your Power BI implementation/migration. Talk to our BI consultants about it today – CLICK HERE
You can also get in touch with us about-
Here are 5 clever workarounds for creating remarkable reports in Power BI that the Business Intelligence tool won’t tell you about on its own.
Power BI transforms your Big Data into appealing and insightful visuals that you can absorb in a glance for enhancing your decision-making process. But to push the Business Intelligence tool to get the most out of your Big Data and create some stunning reports, you need a little extra help. We have discovered 5 clever workarounds you can use in Power BI that will make the Business Intelligence tool work more effectively for you and give you greater results.
Hide and show slicer pane in the Business Intelligence tool:
- Select a right arrow button and place it on the left side of the screen.
- Go to view tab and enable ‘Bookmarks Pane’ and ‘Selection Pane’.
- Add a bookmark and name it as ‘Slicer Pane Invisible’.
- Select a rectangular shape and place your slicers on top of it with a left arrow button which indicates as to close.
Make sure you bring the slicers, rectangular shape (which acts as placeholder) and the left arrow on top in the respective order by ‘bring forward/send forward’ in the Format Tab.
- Now add a bookmark and name it as ‘Slicer Pane Visible’.
- You must hide the right arrow which will be hiding behind the slicer pane (You can do this by going to View tab and check the selection pane. In the selection pane you can find the right arrow button and hide it.) and update the bookmark for ‘Slicer Pane Visible’.
- Next you must hide the slicers, rectangular shape and the left directed arrow mark from the selection pane and update the bookmark of ‘Slicer Pane Invisible’.
- You must uncheck the data option from both the bookmarks so that the data is not recorded.
- Assign the ‘Slicer Pane Visible’ bookmark to the right directed arrow button.
- Assign the ‘Slicer Pane Invisible’ bookmark to the left directed arrow button after selecting on the ‘Slicer Pane Visible’ bookmark.
Change date hierarchy on axis using bookmarks in Power BI:
- You can add hierarchical elements (day, month, quarter, year) into the axis and achieve drilldown using the drilldown option. However, the challenge is when there are too many charts and you cannot manually use the drilldown option for each chart. This is where you can make use of bookmarks.
- At first, you create charts with one hierarchical element (either day/month/quarter/year) in a page. All you need to do next is to just duplicate the page and change the hierarchical element in the axis. Suppose you created charts using ‘months’, change it to ‘year’.
- The next step to be followed is to bookmark these pages and assign them to the buttons.
- Place the buttons on the main page.
- Remember to assign the bookmarks to the buttons of both pages and sync the slicers.
Hide slicer values in Business Intelligence:
- You have no option to remove the slicer values that are not needed.
- So, in this case you can must create two slicers with the same field.
- You can choose values that you want from any of the two slicers that you have.
- The values that you chose from a slicer (to be hidden) acts as an input to the second slicer.
- Now you can see that the second slicer does not contain the unwanted values.
- The next step to be followed is to navigate to the view tab and enable selection pane.
- In the selection pane, hide the slicer where you have selected only the items that you need.
Combine all measures in one table while using Power BI:
- Sometimes so many measures are created that it is difficult to find them. So, in this case you can organize all the measures in a table which is dedicated only to measures.
- Create a new table (doesn’t matter even if the column values are empty).
- Now create one measure in the table and delete the empty column.
- All your measures can be created in the same table now.
- Here you can easily find your measures.
Power BI is more powerful and effective than you think. With the above shortlisted clever workarounds, it’s time to embrace the actionable insights of this Business Intelligence tool, and let your Big Data speak to you like never before.
Measure more than 1 value on the same axis (Disconnected slicers):
- Assuming, you have sales Big Data with dimensions such as MSRP, quantity and sales.
- Suppose you want to switch and see the Big Data on a same chart i.e. by using the slicer options you want to select between profits, quantity or sales.
- Then in this case you can create a new table by entering the measurements name (i.e. sales, quantity and profits in different rows) into a new table (go to enter data and enter the text).
- Now you have a new table with a column that can be used as slicer.
- The next step is to write a DAX function using the switch condition. Here is an example-:
Value Selection =
VAR Selection =
SELECTEDVALUE (‘Switch table'[Slicer Selection], “All”)
RETURN
SWITCH (TRUE (),Selection = “Sales”, Sum(sales_data_sample[SALES]),
Selection = “Quantity”,
Sum(sales_data_sample[QUANTITYORDERED]),
Selection= “MSRP”, SUM (sales_data_sample[MSRP]),
Sum(sales_data_sample[SALES]))
Here you create a variable called as “Selection”. When the selected value is all then it returns sum of sales. When ‘sales’ is the selected value (in the slicer) then sum of sales is returned and vice versa for profit and quantity.
- Now drag the created measure into the chart with dimension of your choice (could be date, region).
- Next, using the column that you created in the table manually, you can get that column into the slicer field and switch between MSRP, quantity and sales. Relevant values will be shown as per slicer selection.
