Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
siddhantk989
Helper III
Helper III

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.

Capture.PNG

The Second table also has 3 columns for Fiscal Year, total Sales and Total Units

Capture1.PNG

 

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.

 

1 ACCEPTED SOLUTION
BetterCallFrank
Resolver IV
Resolver IV

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

 

 

View solution in original post

5 REPLIES 5
BetterCallFrank
Resolver IV
Resolver IV

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

 

 

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?

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.