Forum Discussion

jmhoskinson's avatar
jmhoskinson
Helper I
6 years ago

Change X Axis Dynamically

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?

Need Help

9 Replies

    • jmhoskinson's avatar
      jmhoskinson
      Helper I
      6 years ago

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

      • Icey's avatar
        Icey
        Community Support
        6 years ago

        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.

  • Icey's avatar
    Icey
    Community Support
    6 years ago

    Hi jmhoskinson ,

     

    What is the structure of your model?

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

     

     

    Best Regards,

    Icey

      • Icey's avatar
        Icey
        Community Support
        6 years ago

        Hi jmhoskinson ,

         

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

         

         

        Best Regards,

        Icey

    • jmhoskinson's avatar
      jmhoskinson
      Helper I
      6 years ago

      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.