Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to 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
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
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.
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
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?
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |