Forum Discussion

Jansco's avatar
Jansco
Helper I
7 years ago
Solved

Is this possible using the SWITCH function or is there another way?

I followed the tutorial in this video ( https://www.youtube.com/watch?v=cgPnnPBwAq) and have been able to create a somewhat functioning version of a dynamic report in Power BI. When I click on various metrics ("Day over Day" and "Day over Week", etc) a table changes to include the metric selected. My question is, can I somehow include multiple columns within the SWITCH function? So, for example, when I click on the "Day over Day" BUTTON my table should change to include "Cost Day over Day" AND "Profit Day over Day", and so on.

Here is the code I am using:

Selected Metric = 
SWITCH( TRUE(),
    [Metric Selected] = "DoD", FORMAT([Total Cost DoD],0),
    [Metric Selected] = "DoW", FORMAT([Total Cost DoW], 0))

Or, another question might be, is there a way to return multiple columns of data using one measure?  Maybe a loop that will create multiple measures in one function?

  • Hi Jansco,

     

    On the table where you define the Metric Selected you can create a grouping level and used it as your slicer, so you should have a table with:

     

    Level 1 Measure
    Day Over Day Cost Day over Day
    Day Over Day Profit Day over Day
    Month Over Month Cost Month over month
    Month Over Month Profit Month over month

     

    Using the Level 1 on your slicer you will get both measures on your chart or visuals.

     

    Regards,

    MFelix

1 Reply

  • Hi Jansco,

     

    On the table where you define the Metric Selected you can create a grouping level and used it as your slicer, so you should have a table with:

     

    Level 1 Measure
    Day Over Day Cost Day over Day
    Day Over Day Profit Day over Day
    Month Over Month Cost Month over month
    Month Over Month Profit Month over month

     

    Using the Level 1 on your slicer you will get both measures on your chart or visuals.

     

    Regards,

    MFelix