Change X Axis Dynamically

[jmhoskinson]

Hi Everyone, 

I'm wanting to dynamically change my X axis for a bar chart. If the Regions filter has an option selected, I want to X axis to show account executives; if the Regions filter is not filtered, I want the X axis to show the regions (see attached pics). I've seen the other threads, and they won't work for my situation - I don't want to change my data model because it's pretty fragile. I want to do this entirely with DAX.

I've taken a few stabs at it (mostly combinations of IF/SWITCH(TRUE()) and ISFILTERED), but I'm struggling with my result not being scalar. Any help?

[Icey]

Hi @jmhoskinson ,

Please share me the link.

Best Regards,

Icey

[jmhoskinson]

ok that's wierd. I posted it yesterday... I guess it didn't go through... use this link.

[Icey]

Hi @jmhoskinson ,

Please check:

1. Create tables.

RegionAccount =
VAR t1 =
    SUMMARIZE ( Users, Users[Region], Users[Account Executive] )
VAR t2 =
    ADDCOLUMNS ( t1, "Axis Dimension", "Region", "Axis Value", [Region] )
VAR t3 =
    ADDCOLUMNS (
        t1,
        "Axis Dimension", "Account Executive",
        "Axis Value", [Account Executive]
    )
RETURN
    UNION ( t2, t3 )

Region = DISTINCT(Users[Region])

Account Executive = DISTINCT(Users[Account Executive])

2. Create relationships.

3. Create measures.

Axis Dimension Selected =
SWITCH (
    TRUE (),
    ISFILTERED ( Users[Region] ), "Region",
    ISFILTERED ( Users[Account Executive] ), "Account Executive"
)

CountDeal = COUNT(Deals[Deal Owner])

Count =
IF (
    NOT ( ISBLANK ( [Axis Dimension Selected] ) ),
    SWITCH (
        [Axis Dimension Selected],
        "Region", CALCULATE (
            [CountDeal],
            USERELATIONSHIP ( RegionAccount[Account Executive], 'Account Executive'[Account Executive] ),
            FILTER ( RegionAccount, [Axis Dimension] <> "Region" )
        ),
        "Account Executive", CALCULATE (
            [CountDeal],
            USERELATIONSHIP ( RegionAccount[Region], Region[Region] ),
            FILTER ( RegionAccount, [Axis Dimension] <> "Account Executive" )
        )
    )
)

4. Create visuals.

5. Test.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jmhoskinson]

Hi @Icey and @amitchandak - I really appreciate the help, but my requirement is that I can't be doing anything new to my data model.

[amitchandak]

@jmhoskinson , You might have to use a bookmark for this

https://radacad.com/bookmarks-and-buttons-making-power-bi-charts-even-more-interactive

[jmhoskinson]

@amitchandak I had thought of that idea, but for a simple user experience, I'm trying to make it one click on a filter they will already use.

[Icey]

Hi @jmhoskinson ,

What is the structure of your model?

Please share me a dummy PBIX file, removing sensitive information, for test.

Best Regards,

Icey

[jmhoskinson]

@Icey I'm not seeing an option to upload my dummy file. Am I missing it?

[Icey]

Hi @jmhoskinson ,

I recommend you to upload your dummy file to OneDrive for Business and then paste the link here.

Best Regards,

Icey