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
BuistJF
Frequent Visitor

Toggle between different measures

Not sure if this is possible in PBI and from looking at it paremeters maybe the way forward or whether there is an option to dynamically adjust the DAX shown below.

 

I have a line chart that has 3 measures (3 month cancelled, 3 month active, 3 month difference) which are all 3 month moving averages (dax measure) and the chart shows data over a time period. I have a further 6 measures that are the same as above but for a 1 & 2 month moving average. What I want to do is have a filter / slicer / toggle where I can change between 1 Month, 2 Month or 3 Month moving average. The chart will only ever show either 1, 2 or 3 never combined or summed.

 

Example of the DAX measure used is shown below if helpful:

3 Month Active =
CALCULATE (
    AVERAGEX ( DSET_3_Month, DSET_3_Month[Activate Count] ),
    DATESINPERIOD (
        DSET_3_Month[Date],
        LASTDATE ( DSET_3_Month[Date] ),
        -3,
        MONTH
    )
)

Thanks for the help in advance

1 ACCEPTED SOLUTION
djurecicK2
Super User
Super User

Hi @BuistJF , 

 You could create a disconnected table to use for the slicer.

https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-relationships-understand#disconne...

 

Create a new measure using SelectedValue to get the selected value from that slicer:

https://learn.microsoft.com/en-us/dax/selectedvalue-function

 

Then use a Switch statement to determine which measure to return based on the value from above

https://learn.microsoft.com/en-us/dax/switch-function-dax

 

View solution in original post

1 REPLY 1
djurecicK2
Super User
Super User

Hi @BuistJF , 

 You could create a disconnected table to use for the slicer.

https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-relationships-understand#disconne...

 

Create a new measure using SelectedValue to get the selected value from that slicer:

https://learn.microsoft.com/en-us/dax/selectedvalue-function

 

Then use a Switch statement to determine which measure to return based on the value from above

https://learn.microsoft.com/en-us/dax/switch-function-dax

 

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.