Latest Enterprise DNA Initiatives

Dynamic Measure Based on Dimension Slicer Selection

Hi ,

Is there any method where we can change measure column within horizontal bar chart using dimension slicer. I can’t use key measure disconnected table method. All fields are from same table from SQL Server via direct query mode

When Category AMW is selected then I need to calculate countrows(post rating) in Bar Chart and when Category SMP is selected I need to calculate countrows(priority rating) in Bar Chart

Please guide

Thanks
Archer

@Archer,

Do you have the ability to use Calculation Groups? @MudassirAli has a video on this very question using the small multiples visual and calculation groups coming out soon, so I know it will work for import mode.

Update – I did some research on Stack Overflow and it seems Calculation Groups are compatible with Direct Query mode.

  • Brian

Hey @BrianJ, Many thanks for your response ! Wish you a happy new year

I am attaching sample of what I would want but in Single Slicer selection so when anyone selects Franchise AMW then we need to show Visuals on First Page and when other two values of Franchise are Selected then we need to show same visual but switch the measure from Post Value to Rate and vice versa. When All is Selected, can we display a guided message asking users to select AMW or two other values combinedly or separately

Is this possible using Calculation Groups? I learnt that it is used for measure variations like for Time Calculations and my learning curve is slim as you may have found it in the past :slight_smile:
I hope you would try to get me a solution for this scenario !

Many thanks
Archer

Dynamic Measure Change Scenario.pbix (1.6 MB)

@Archer,

Happy New Year to you too. I think I’ve come up with a neat solution on this one for you.

Check it out in the service first, since you don’t have to worry about click versus ctrl-click there. Just make a selection(s) in the Franchise slicer and click the adjacent button, which should route you to the correct visual or direct you to select a different combination.

image

The way I have done this is to create a disconnected Franchise table with the slicer selections in it. Then created the adjacent button next to the slicer, tied to a conditional page navigation action invoking this measure:

Route Page =

VAR Selections =
VALUES( Franchise[Selection] )

VAR Page =
SWITCH( TRUE(),
    AND( COUNTROWS( Selections ) = 1, "AWM" IN Selections ), "AWM",
    "AWM" IN Selections, "Other",
    "SPM"
)

RETURN 
Page

which routes to the proper page based on the SWITCH/TRUE statement logic within the measure derived from your requirements. Copied this slicer/button combo to all three pages and synced the slicers so that regardless of which page you ended up on it would reflect the choices made prior to hitting the button.

I hope this is helpful and meets your requirement. Full solution file attached below.

– Brian
eDNA Forum - Dynamic Measure Change Scenario Solution.pbix (1.6 MB)

PS – once I saw the complexity of the slicer logic, I decided to abandon the calculation groups idea.

Awesome technique @BrianJ

Thank you for this neat solution! Just wanted to know if this is possible with Bookmarks ? instead of page nav

Thanks
Archer

@Archer,

I’m not going to say it’s impossible to do this with bookmarks, because every time I say something is impossible in Power BI, someone more clever proves me wrong. :grinning:

That being said, I don’t see an obvious path to doing this via bookmarks, since unlike page navigation bookmarks don’t have a conditional branching option. Personally, I find page navigation far easier to set up and maintain than bookmarks, even though it does involve some file bloat in terms of extra report pages.

  • Brian
4 Likes