Power BI, the upcoming self-service BI tool from Microsoft has been making great strides in terms of feature improvements and usability in the recent past. Despite this, there is no direct way yet that provides the user the option of choosing a specific measure for display in a visualization.
Consider a scenario where you would like to show Sales and Production of a particular commodity across a dimension – say date or location in the same visualization. Here the user needs to have the latitude to select one specific measure – Sales or Production. In cases where the data may have Actuals, Budget and Forecast, but the user may be interested only in Actuals.
In Tableau, one may consider using the measure value and measure name fields that are auto generated for achieving the required result; or one could create a parameter and create two fields whose value is based on the parameter selected. Since the Measure Name and Measure Value fields are not available in Power BI, we emulated the second method in Power BI.
The first step is to create a column of selections for the user to choose from. We achieve this by creating a table with only one column by using the enter data icon. Note that his table would have no relation to the actual data set. It is there only to capture the user’s preference as an input.
We then add the Select measure column to the values tab of a slicer. We also make sure that single selection is enabled in the properties of the slicer to make sure that the user is able to make only one selection at a time.
We then three calculated columns to represent the actual data that we would be using in the visualization. We architect the DAX expression such that each calculated column represents the values of the measure that is part of the selection made in the slicer. Here, When Measure 1 is selected in the slicer we would display Measure 1-Actual, Measure 1-Forecasted and Measure 1-Budget; when the other two selections are made we would only display the Actuals.
|Measure 1||Measure 1-Actual||Measure 1-Budget||Measure 1-Forecasted|
|Measure 2||Measure 2||Null Value||Null Value|
|Measure 3||Measure 3||Null Value||Null Value|
When you make a selection in the slicer, the Table that you created has only one value so that value is returned to the switch statement by the ALLSELECTED function. The switch statement compares it with the entered values and assigns values to the calculated fields accordingly.
We use null value instead of zero because we don’t want a line along the x axis when using a visualization like line chart.
Now we add the created fields to the required chart and watch the measures displayed change based on the selection made in the slicer.
When the first selection is made.
When the second selection is made
When the third selection is made.
Want to know more? Reach out to us at firstname.lastname@example.org