cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ScottG Frequent Visitor
Frequent Visitor

How can I switch the measure a chart or table is using based on a slicer choice?

Good Day,

 

We are creating a Power BI report where the data source is a live SSAS Tabular Cube.

 

We would like to create a number of pages within the report where the report user can switch which measure they are looking at by clicking on the measure name in say a chiklet slicer. So the whole page would "swap" it's selected Measure to the one selected.

 

Can anyone point me in the right direction please? 

2 ACCEPTED SOLUTIONS

Accepted Solutions
asocorro Member
Member

Re: How can I switch the measure a chart or table is using based on a slicer choice?

If you mean something like this:

 

pm.png

 

Then it's all in the data model.  These are the (simplified) two main tables supporting the report above:

 

model.png

 

In our case, we have three similar but separate pages (and three corresponding measure tables), because the measures have different data types (percentage, currency, and whole number).

Connect with me in LinkedIn: https://pr.linkedin.com/in/adolfosocorro
Follow me on Twitter: https://twitter.com/AdolfoSocorro
Highlighted
anithat Regular Visitor
Regular Visitor

Re: How can I switch the measure a chart or table is using based on a slicer choice?

Hi ScottG

 

Please refer to below link and let me know if you have any further queries.

 

http://www.bipatterns.com/dynamic-powerbi-reports-via-dax/

 

Measures on a slicer is well explained in this.

8 REPLIES 8
asocorro Member
Member

Re: How can I switch the measure a chart or table is using based on a slicer choice?

If you mean something like this:

 

pm.png

 

Then it's all in the data model.  These are the (simplified) two main tables supporting the report above:

 

model.png

 

In our case, we have three similar but separate pages (and three corresponding measure tables), because the measures have different data types (percentage, currency, and whole number).

Connect with me in LinkedIn: https://pr.linkedin.com/in/adolfosocorro
Follow me on Twitter: https://twitter.com/AdolfoSocorro
ScottG Frequent Visitor
Frequent Visitor

Re: How can I switch the measure a chart or table is using based on a slicer choice?

Thank you for the suggestion I will attemp this later tonight.

 

So in this case the Measure and Measure Rates tables become a dimensions?

 

Is there any DAX required for this approach? Sorry quite new to DAX (done lots of MDX)...

 

Is there no way to put all the measures (different formats) in one slicer so that format is drawn from the actual measure's formatting in the SSAS Cube?

ScottG Frequent Visitor
Frequent Visitor

Re: How can I switch the measure a chart or table is using based on a slicer choice?

So far I have been able to include the Measure Table with Measure Name as a slicer, however how does this relate back to the fact tables in this case? I.E. how does Power BI know to filter to that column or calculated measure for the fact table?

 

My appologies as I mentioned I am quite new to Tabular.

Highlighted
anithat Regular Visitor
Regular Visitor

Re: How can I switch the measure a chart or table is using based on a slicer choice?

Hi ScottG

 

Please refer to below link and let me know if you have any further queries.

 

http://www.bipatterns.com/dynamic-powerbi-reports-via-dax/

 

Measures on a slicer is well explained in this.

ScottG Frequent Visitor
Frequent Visitor

Re: How can I switch the measure a chart or table is using based on a slicer choice?

This looks quite clear, Thank you for the sample! I will try it out first thing tomorrow.

ronrule Regular Visitor
Regular Visitor

Re: How can I switch the measure a chart or table is using based on a slicer choice?

This link is no longer valid. Does anyone have the example archived? Thanks!

B_Real Regular Visitor
Regular Visitor

Re: How can I switch the measure a chart or table is using based on a slicer choice?

@anithat the link you supplied does not work (error 404 page not found). Can you update?

Ishanm959 Frequent Visitor
Frequent Visitor

Re: How can I switch the measure a chart or table is using based on a slicer choice?

Suppose u have entered data into a static table with values "Measure1" and "Measure2" 

[Measure1] and [Measure2] are the two measures in your fact table.

 

You should check in the Relationships view that the fact table and the static table(for slicer) dont have any relationship

defined on them.

 

Next create another Measure using DAX 
Metric Selection = SWITCH(TRUE(),
VALUES('Static Table'[MeasureName]) = "Measure1",[Measure1]
VALUES('Static Table'[MeasureName]) = "Measure2",[Measure2],
BLANK())

 

and then use the [Metric Selection] Measure in your chart and toggle between the measures using slicers.