Forum Discussion

ScottG's avatar
ScottG
Icon for Advocate II rankAdvocate II
10 years ago
Solved

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 whi...
  • asocorro's avatar
    asocorro
    10 years ago

    If you mean something like this:

     

     

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

     

     

    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).

asocorro's avatar
asocorro
Icon for Skilled Sharer rankSkilled Sharer
10 years ago

If you mean something like this:

 

 

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

 

 

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).

ScottG's avatar
ScottG
Icon for Advocate II rankAdvocate II
10 years ago

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.

    • B_Real's avatar
      B_Real
      Icon for Advocate IV rankAdvocate IV
      9 years ago

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

    • ScottG's avatar
      ScottG
      Icon for Advocate II rankAdvocate II
      10 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

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

  • Ishanm959's avatar
    Ishanm959
    Frequent Visitor
    8 years ago

    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.