Change x-axis categories dynamically

[Ola]

Our idea is to change the x-axis dynamically based on a slicer selection.

Is this possible, via some DAX measure ?

Kind regards, Ola

 

Sample file: DOS sample file.pbix

We have two Tables: 1. 'Consolidated', 2. 'Report area level'

'Report area level' has 1 column with 2 rows: "External area", "Rep area"   .... used as a slicer

'Consolidated' has 3 columns; "EXTERNAL_AREA", "REP_AREA2", "Value"  .... the areas are 2 types of geographically clustered areas.

I tried to add a Calculated column in table 'Consolidated':

Selected area =
IF(SELECTEDVALUE('Report area table'[Report area name];"")="External_Area";'Consolidated'[EXTERNAL_AREA];
IF(SELECTEDVALUE('Report area table'[Report area name];"")="Rep area";'Consolidated'[REP_AREA2];
""))

Which I understand is not possible: https://community.powerbi.com/t5/Community-Knowledge-Base/Calculated-Column-Table-Change-Dynamically...

[v-jiascu-msft]

Hi @Ola,

Please share a dummy sample in a .pbix file. And the result you want?

If the field of the slicer is the same with x-axis, the x-axis will change dynamically. For example, there are three [Year] 2008, 2009, 2010 in the x-axis, it will change automatically based on the selection of the slicer. But if you want to change the x-axis from [Year] to [Country], I'm afraid this isn't supported. Please submit an idea here: ideas.powerbi.

I wonder if this is what you want.

Best Regards!

Dale

[Ola]

Ok, here is the attempt (.pic + .pbix):

DOS sample file.pbix

[v-jiascu-msft]

Hi @Ola,

A Calculated Column can't be changed dynamically by the slicer. Maybe this is what you want. Add two columns in the axis. You can change the axis by click "Go to next level".

Best Regards!

Dale

[Ola]

Thank for the reply Dale. Sorry to say, is not a hierarchy.

It is two completely unrelated ways to group countries.

I wish to switch between the two groups, as two different views.

... via a Calculated Measures, or some trick or ... ?

I was hoping for something like =SELECTEDVALUES() that could return some sort of "Calculated measure-array/table", or...

Do you think it is possible ? Or is that 'the limit of DAX' ?

[richbenmintz]

I do not think that this is possible, however the new Bookmarking feature would allow you to provide this type of functionality.

You can create a bookmark for your 3 possible axis scenarios and then navigate to the different scenarios using the bookmarks well.

link below describes bookmarking in detail.

https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-bookmarks/.

Good luck

[Ola · Accepted solution]

Solved it with a ' resonable work-around', via PQ:

1. I unpivot the two columns, (which duplicates all rows) but

2. I set a Chiclet slicer to require a selection, and 

3. I then just have pay attention to the duplicates, and everything should be fine.

Thanks for all suggestions