topic Re: Change X Axis Dynamically in Desktop

Change X Axis Dynamically

jmhoskinson — Mon, 20 Apr 2020 22:24:02 GMT

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?

Re: Change X Axis Dynamically

Icey — Tue, 21 Apr 2020 01:56:15 GMT

Hi jmhoskinson ,

What is the structure of your model?

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

Best Regards,

Icey

Re: Change X Axis Dynamically

amitchandak — Tue, 21 Apr 2020 03:02:17 GMT

jmhoskinson , You might have to use a bookmark for this

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

Re: Change X Axis Dynamically

jmhoskinson — Tue, 21 Apr 2020 19:41:25 GMT

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

Re: Change X Axis Dynamically

jmhoskinson — Tue, 21 Apr 2020 19:42:38 GMT

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.

Re: Change X Axis Dynamically

Icey — Wed, 22 Apr 2020 01:26:29 GMT

Hi jmhoskinson ,

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

Best Regards,

Icey

Re: Change X Axis Dynamically

Icey — Thu, 23 Apr 2020 09:38:20 GMT

Hi jmhoskinson ,

Please share me the link.

Best Regards,

Icey

Re: Change X Axis Dynamically

jmhoskinson — Thu, 23 Apr 2020 12:52:54 GMT

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

Re: Change X Axis Dynamically

Icey — Fri, 24 Apr 2020 09:45:20 GMT

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.

Re: Change X Axis Dynamically

jmhoskinson — Fri, 24 Apr 2020 15:21:24 GMT

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.