List items selected in both slicers

posted in DAX on by
Share The Knowledge

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:

2020 U.S. Election (states selected in both slicers)Download

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)

51 rows in table ElectoralVotes

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).

Error: these 3 states AREN’T selected in both slicers

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.

Arizona and Michigan are selected in both slicers.

Below, if a slicer has zero selections then ‘Selection Errors’ is empty.

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


Share The Knowledge