Dynamically choose columns based on slicer

[tchamberlain99]

I have a table with 4 values :  City Name, State Code,  Data1, Data2.   I am using a Clustered Column chart to Visualize this data.

I added a slicer that Selects off the City Name.   If I choose a city the entire chart just shows the one city I selected.

What I would like to do is based on that one city I selected show all the cities in that state?  What tool should I be using to get the state from the slicer selection and have the clustered chart show all those columns with that state?

[Anonymous · Accepted solution]

HI @tchamberlain99,

Normal slicer not support operation what you mentioned, you need a few complex steps to achieve similar effect.

First, you need to create new table with city and its percents as source of slicer.

Selector =
DISTINCT (
    SELECTCOLUMNS ( table, "City Name", [City Name], "State Code", [State Code] )
)

Second, write a measure to get selected item's parent and use it as condition to compare with original table row content to return filter tag.

In Range =
VAR _list =
    CALCULATETABLE ( VALUES ( Selector[State Code] ), ALLSELECTED ( Selector ) )
VAR _current =
    SELECTEDVALUE ( table[State Code] )
RETURN
    IF ( _current IN _list, "Y", "N" )

Finally, apply filter tag on visual level filter to remove unrelated records.(drag to visual level filter and keep item "Y")

Regards,

Xiaoxin Sheng

[tchamberlain99]

Thank you for your response.

I'm a little confused about where the first bit of code gets entered in or how it turns into a slicer.   I believe the second bit of code I got,  I just add a measure to table i'm visualizing.  Though of course at this point its still complaing that it can't figure out what Selector is.   

What steps would I need to do to get to the point I can enter in the code for the Selector.  I tried adding it as a new query and that didn't create a new table.

[Anonymous]

Hi @tchamberlain99,

Selector is a new calculated table based on my code(dax formula).

After finish create it, you can use city column as source to create a slicer.

Regards,

Xiaoxin Sheng

[tchamberlain99]

I have a table with 4 values :  City Name, State Code,  Data1, Data2.   I am using a Clustered Column chart to Visualize this data.

I added a slicer that Selects off the City Name.   If I choose a city the entire chart just shows the one city I selected.

What I would like to do is based on that one city I selected show all the cities in that state?  What tool should I be using to get the state from the slicer selection and have the clustered chart show all those columns with that state?