slicer based on columns

[siddhantk989]

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.

[BetterCallFrank · Accepted solution]

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

[siddhantk989]

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. 

[BetterCallFrank]

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?

[siddhantk989]

Hi @BetterCallFrank,

Thanks for the reply. But the 2 tables are dynamic tables which I am creating form the main table based on different logics for year, sales and units,  so I can not merge them together. Also I think we can not unpivot the dynamic tables. Is there anything that I can do with the If statement that we are using to say that if my IF is false just show me the details of both sales and units?

Thanks in advance.

Regards,

Siddhant

[siddhantk989]

Thnaks a lot Frank. The solution is working. Thanks again for your help