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