Forum Discussion
slicer based on columns
I have created 2 different dynamic tables in POWER BI from the main table. The first table has 3 columns for Year, Total sales and Total Units.
The Second table also has 3 columns for Fiscal Year, total Sales and Total Units
Now I have a bar graph in powerbi to show the comparision between sales and units of both tables based on Year. What I want to do is add a filter to my dashboard based on Sales and Units in which if I select Sales The bar graph will only show the data based on Sales and if I select Units the bar graph should show data based on Units.
Note: We can not Unpivot dynamic tables in PowerBI so that is not an option.
Hi,
something along the lines of:
1) create a help-table with 1 col, 2 rows:
DisplayBy
------
Sales
Units
2) In each table create a measure
DisplayValue = IF( HASONEVALUE ( DisplayBy[DisplayBy] ), IF( VALUES( DisplayBy[DisplayBy] ) = "Sales", SUM( Table1[total Sales] ), SUM (Table1[Total Units] ) ), "Please select only one measure to display from the slicer" )
3) do the same as 2 for the other table
4) create a slicer on the DisplayBy[DisplayBy] column.
5) replace the original Measures/Columns in your chart with the new ones and try the slicer
Should work :-)
HTH,
Frank
5 Replies
Hi,
something along the lines of:
1) create a help-table with 1 col, 2 rows:
DisplayBy
------
Sales
Units
2) In each table create a measure
DisplayValue = IF( HASONEVALUE ( DisplayBy[DisplayBy] ), IF( VALUES( DisplayBy[DisplayBy] ) = "Sales", SUM( Table1[total Sales] ), SUM (Table1[Total Units] ) ), "Please select only one measure to display from the slicer" )
3) do the same as 2 for the other table
4) create a slicer on the DisplayBy[DisplayBy] column.
5) replace the original Measures/Columns in your chart with the new ones and try the slicer
Should work :-)
HTH,
Frank
Thnaks a lot Frank. The solution is working. Thanks again for your help
Hi BetterCallFrank. One last question. In case when my IF statement is false instead of showing
"Please select only one measure to display from the slicer"
message. I want to show the data both for sales and Units. Is there any way to do that? I am using the statement as SUM(Table1[total sales] && SUM(Table1[total Units]). But it is not working.
Hi,
yes, this is also possible but the data model needs some rework.
1) restructure tables so that they look like this:
Year|Type|Value
2012|Units|1000
2012|Sales|10000
2013|Units|2000
2013|Sales|29999
and so on
2) create a relationship from colum "Type" to helper table from previous solution
3) yay, no need for calculated measures in this case
4) create grouped column chart from Value Column, put DisplayBy from Helper table on categories, Year on Axis
5) Slicer from DisplayBy Column and chart from this new table can also be used to display both values at the same time
Is this what you're looking for?
