I have 2 slicers based on the same column of U.S. states.
Requirement
Display a list of states selected in both slicers (each one should only be selected in one slicer).
Files: Pbix
Below is zipped pbix file:
Final Measure
Selection Errors = VAR _harvestSwDem = values(ElectoralVotesDSwing[State]) //harvest slicer selections VAR _harvestSwRep = values(ElectoralVotesRSwing[State]) //harvest slicer selections VAR _statelist = CONCATENATEX(INTERSECT(_harvestSwRep,_harvestSwDem),ElectoralVotesRSwing[State],UNICHAR(10)) //create list of states found in both slicers VAR _countDem = COUNTROWS(_harvestSwDem) //count harvested slicer selections VAR _countRep = COUNTROWS(_harvestSwRep) //count harvested slicer selections VAR _countOriginal = COUNTROWS(FILTER(ElectoralVotes,ElectoralVotes[RepDem] = "Swing")) //count orig tbl sw states VAR _final = if(OR(_countOriginal-_countRep=0,_countOriginal-_countDem=0),blank(), _statelist) //if either slicer is empty then blank else display _statelist (concatenated states) RETURN _final
Set-up
Concept
I created table ElectoralVotes by pasting the values into a new table (on the ribbon select: Home / Enter data).
Many states are certain to be Republican or Democrat but many Swing states could go either way. It’s tedious to open the table in power query (Home / Transform data) and manually change items in column ‘RepDem’.
Using 2 slicers we can quickly flip Swing states back and forth between Republican and Democrat.
Raw Data
Table: ElectoralVotes
Base table with all states (also used to create 2 tables further below)
![](https://practicepowerbi.com/wp-content/uploads/2020/11/Table-ElectoralVotes.png)
Table: ElectoralVotesDSwing
Add table using DAX (‘State’ to be used as slicer for Dem swing states)
ElectoralVotesDSwing = FILTER(ElectoralVotes,ElectoralVotes[RepDem] = "Swing")
Table: ElectoralVotesRSwing
Add table using DAX (‘State’ to be used as slicer for Rep swing states)
ElectoralVotesRSwing = FILTER(ElectoralVotes,ElectoralVotes[RepDem] = "Swing")
2 Slicers: Democrat & Republican
Add 2 slicers using column ‘State’ from tables ElectoralVotesDSwing and ElectoralVotesRSwing.
How can we display states selected in both slicers?
Solution
Measure ‘Selection Errors’ is listed above in section ‘Final Measure’. Let’s examine each part.
Harvest slicer selections
I use 2 variables to harvest/get slicer selections inside the measure:
VAR _harvestSwDem = values(ElectoralVotesDSwing[State]) //harvest slicer selections Dem VAR _harvestSwRep = values(ElectoralVotesRSwing[State]) //harvest slicer selections Rep
States selected in both slicers
INTERSECT uses harvested values above to determine states selected in both slicers. CONCATENATEX lists them with a carriage return between each one using UNICHAR(10).
VAR _statelist = CONCATENATEX(INTERSECT(_harvestSwRep,_harvestSwDem),ElectoralVotesRSwing[State],UNICHAR(10)) //create list of states found in both slicers
There’s a complication. If either slicer has no selections my measure doesn’t work (as ‘Selection Errors’ should be empty).
![](https://practicepowerbi.com/wp-content/uploads/2020/11/INTERSECTION-of-slicer-values-complicates-error-display-1024x613.png)
Is either slicer empty?
Variables count both slicer selections and all swing states (original table).
VAR _countDem = COUNTROWS(_harvestSwDem) //count harvested slicer selections VAR _countRep = COUNTROWS(_harvestSwRep) //count harvested slicer selections VAR _countOriginal = COUNTROWS(FILTER(ElectoralVotes,ElectoralVotes[RepDem] = "Swing")) //count orig tbl sw states
If a slicer is empty it counts 9 selections!
I added IF and OR functions below to check for this. If either slicer is empty (has 9 swing states same as original table) then BLANK() else if both aren’t empty then display variable _statelist.
VAR _final = if(OR(_countOriginal-_countRep=0,_countOriginal-_countDem=0),blank(), _statelist) //if either slicer is empty then blank else display _statelist (concatenated states)
Final Visual
Both slicers have selections. Measure ‘Selection Errors’ only displays states selected in both slicers.
![](https://practicepowerbi.com/wp-content/uploads/2020/11/Final-Visual-works-1024x614.png)
Below, if a slicer has zero selections then ‘Selection Errors’ is empty.
![](https://practicepowerbi.com/wp-content/uploads/2020/11/Final-Visual-works-1-slicer-is-empty-1024x620.png)
Summary
This was great practice over several hours during U.S. election week!
Do you have a better solution? If so please share in the comments below.
DAX function links
- VALUES https://dax.guide/values/
- CONCATENATEX https://dax.guide/concatenatex/
- INTERSECT https://dax.guide/intersect/
- FILTER https://dax.guide/filter/
- UNICHAR https://dax.guide/unichar/