Dynamic table visual based on Slicer selection

[Kushalavareddy]

In Power BI, I have four columns: Country, State, City, and Amount. I have two slicers: one for Country and one for State, and a table visual. I need to display the table visual in such a way that if nothing is selected in any slicer, it should show the Country and Amount columns. If a country is selected in the Country slicer, the table should show the State and Amount columns. If a state is selected in the State slicer, the table should show the City and Amount columns. How can I achieve this in Power BI?

I have tried using below meaures but it is not working, Can anyone please assist this Power BI novice with this scenario? I'd be very grateful! Thank you PBI Community!

1.CountrySelected = IF(ISFILTERED('Hierarchy'[Country]), 1, 0)
2.StateSelected = IF(ISFILTERED('Hierarchy'[State]), 1, 0)

3.

DynamicValues =

SWITCH(

    TRUE(),

  [CountrySelected] = 1 , SELECTEDVALUE('Hierarchy'[State]),   

[StateSelected] = 1 , SELECTEDVALUE('Hierarchy'[City]),

   SELECTEDVALUE('Hierarchy'[Country])

)

[Kushalavareddy]

Thanks @Anonymous 
I understand but looking for the result to show entire columns and their respective amount,like the images shown intial post image
Please help me if any way to do that in Power BI

[Anonymous]

Hi @Kushalavareddy ,

Below is my test data:

The following DAX might work for you:

DynamicColumn1 = 
SWITCH(
    TRUE(),
    IF(ISFILTERED('Table'[Country]) ,1 ,0) = 0 , MAX('Table'[Country]),
    IF(ISFILTERED('Table'[Country]) ,1 ,0) = 1 && IF(ISFILTERED('Table'[State]), 1, 0) = 0, MAX('Table'[State]),
    IF(ISFILTERED('Table'[Country]) ,1 ,0) = 1 && IF(ISFILTERED('Table'[State]), 1, 0) = 1, MAX('Table'[City])
)

The final output will be like this:

Best Regards,

Xianda Tang

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

[Kushalavareddy]

Hi @Anonymous ,Thanks for your response. 
I have tried with the above DAX but it is showing only MAX records for me each country,state,city.

I have tried to replace max with selectedvalue it showing blank results.
Could you please tell me how you are getting all records using MAX.
Thanks

[Anonymous]

Hi @Kushalavareddy ,

First of all, if you want to use a metric to return an entire column, you can't do it, you can only return a single value.

You say selectvalue returns a blank value because since there are multiple elements in a column that are not selected in the filter, he will return a blank value by default.

The best solution is to remove the aggregation effect from the amout columns:

Best Regards,

Xianda Tang

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