Create a dynamic selector for column selection – Power BI
I am writing this post after quite a long time. I had been busy in a lot of work lately and unable to write anything. Recently, I was working on a power BI report and had to encounter the problem of defining a slicer for selecting the values in the visual. using a slicer is pretty straight forward, but the problem with a slicer is that, you can filter the report based on the column and return the value there. In my case the visual had to change the column to show a different value.
The data had two columns for values, one is with the Quantity of units sold and the other is for the sales value. The requirement is to have a slicer that can switch between the Quantity and Value while all other things remains same.
now in order to create a visual that can switch column based on the selection in slicer, I needed to have a Dax formula to create a dynamic varible that can be updated based on the selection in slicer. to do this I had to create a table by using the following steps.
Create a Table for selector
Click on modelling tab and select New Table
once you click on new table, you will see the DAX command window open right below it
in the field enter the following formula
Quantity/Total selector = UNION(ROW("Type","Quantity"),ROW("Type","Value"))
the details of the above code as as follows
- Quantity/Total selector = This is the name of the table, you can set it to whatever you want to
- UNION() , this will bind the preceeding values in the table
- ROW(“Type”,”Quantity”) , Create a row with header “Type” and the “Quantity” as value
- ROW(“Type”,”Value”), Create a row with header “Type” and “Value” as value
- () and of course the whole argument is wrapped in ()
after you press enter after entering the command, you can see anew table with the table name you just mentioned appearing in the fields window
you can inspect the data in this new table by selecting Data and then selecting the table from fields window.
it is noteworthy that the data has come up in a single column with Type as column header and the values we passed as the values.
Now that we have the selector table for our slicer, we will assign the table as a source to this slicer.
now we need to create a new DAX measure to switch the selection based on the value from this slilcer.
Creating a calculated DAX measure
create a new Measure in the fields window by right clicking in the table and selecting New Measure
You will again get the DAX window, where you need to enter the following formula
Dynamic selection = var selectitem=IF(HASONEVALUE('Quantity/Total selector'[Type]),VALUES('Quantity/Total selector'[Type]),BLANK()) return SWITCH(selectitem,"Quantity",SUM('supermarket_sales - Sheet1'[Quantity]),"Value",SUM('supermarket_sales - Sheet1'[Total]),BLANK())
The details of the above code are as follows
- Dynamic selection = , this is the name of the Measure you are creating, you can set it to whatever you like
- var selectitem=IF(HASONEVALUE(‘Quantity/Total selector'[Type]),VALUES(‘Quantity/Total selector'[Type]),BLANK())return, This piece of code is defining the variables for selection. in this example we have two values to choose from that’s why the line VALUE(‘Quantity/Total selector'[Type]) is appearing twice, if you need to choose from three or more values, you need to repeat this code that many times.
- SWITCH(selectitem,”Quantity”,SUM(‘supermarket_sales – Sheet1′[Quantity]),”Value”,SUM(‘supermarket_sales – Sheet1′[Total]),BLANK()), this piece of code is telling the report to switch between column based on the selection.
after you click enter, you will see a new measure created in the table.
Now in our visual instead of adding the values columns, we will add this newly created measure as value
So that’s how you make a selection filter to dynamically select values. I am attaching the created report and the data set used for your reference and tinkering.
How did you like the article, please do let me know in the comments below. It will help me keep motivated and inspired to write more.