Dynamic Drill-down with ISFILTERED; Modify bar chart axis dimension

[Anonymous]

Hello,

I am trying to determine what needs correction to make this formula/function work.  Or if I need to do something completely different.  

I have 4 separate filters that break the US into regions based on various things (such as sales regions, geographic regions, etc),

and for visuals I have a filled map card and a bar chart card. The map shows the US with regions in the legend, and the bar chart also shows the regions.

I was thinking to create a formula to dynamically select which Regional View to be visualized.  I wasn’t sure if a parameter would be the best for that.  Ideas would be helpful.

I’m thinking to do a parameter as:

Name: Regional View

Min: 1

Max: 4

Increments: 1

And write a DAX:

Regional Selection =

IF( ‘Regional View’[Regional View Value]=1, ‘data’[Region1],

IF( ‘Regional View’[Regional View Value]=2, ‘data’[Region2],

IF( ‘Regional View’[Regional View Value]=3, ‘data’[Region3],

IF( ‘Regional View’[Regional View Value]=4, ‘data’[Region4],

))))

The problem is it doesn’t allow me to select the region as a true statement. Would a switch statement be better?

The second thing I want to do is have a slicer that will update to the region selected, and once selected I want to be able to zoom in on the region, and then show the bar chart by state.

My logic was to use ISFILTERED.  I’m relatively new to DAX so I’m not sure if this formula is possible or not.

Region Filtered =

IF( ISFILTERED( ‘data’[Region1]), ‘data’[State], ‘data’[Region1]),

IF( ISFILTERED( ‘data’[Region2]), ‘data’[State], ‘data’[Region2]),

IF( ISFILTERED( ‘data’[Region3]), ‘data’[State], ‘data’[Region3]),

IF( ISFILTERED( ‘data’[Region4]), ‘data’[State], ‘data’[Region4]),

))))

Again, I cannot select State if true or Region if false.  I believe it is something with the syntax, but I am just getting started with DAX so I’m not real sure what the solution is.

[amitchandak]

@Anonymous , Not very clear.  if you try to change the axis/group by/location on map refer this

Dynamically change chart axis in Power BI
https://www.youtube.com/watch?v=6jeSIRpjv0M

[Anonymous]

I'm really trying to get help with the IF statement.  I feel like the IF statement only works with numerical values and text.

For example IF( expression, 1, 0)  or IF( expression, "One", "Zero").

Can you not write something like IF( expression, [Dimension], [Other Dimension])

In this situation the dimensions are the States in the US and the Regions in the US.

I'm guessing I can create a table with All the states and 1, 2, 3, 4 etc.  Join the table and write a statement, but that seems overly complex.  

[v-kelly-msft]

Hi @Anonymous ，

Could you pls upload your .pbix file to onedrive business and share the link with us(remove the confidential information)?

I would try to test for you.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!