Forum Discussion

Kushalavareddy's avatar
Kushalavareddy
Frequent Visitor
2 years ago

Dynamic table visual based on Slicer selection

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])
)
Need Help
Tips and Tricks

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Kushalavareddy
      Frequent Visitor
      2 years ago

      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's avatar
        Anonymous
        Not applicable
        2 years ago

        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.

  • Kushalavareddy's avatar
    Kushalavareddy
    Frequent Visitor
    2 years ago

    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