DAX measure to respond only to intersection from a Slicer

[Anonymous]

Hi!

We have a simple problem, but the solution does not seem so simple.

We want to select one or multiple languages in a slicer and that the count of users speaking these languages shows the intersection. Meaning that if we select English and French in the slicer, we would see users who speak only English and French (not one or the other, as we are getting at the moment).

This screenshot is from a sample file I have created for the post. 

We should be getting a count of 2, not 4, since only 2 users speak English and French.

The measure should be dynamic (not hard-coded). We found this suggestion online but it does not work completely as we only get the count of users when all languages are selected (1 user in this case):

TEST Measure =

VAR _cat = { "English", "French", "Italian" }

RETURN

COUNTAX(

VALUES( 'User Language'[ID] ),

IF(

CALCULATE(

DISTINCTCOUNT( 'User Language'[Language] ),

TREATAS( _cat, 'User Language'[Language] )

) = COUNTROWS( _cat )

, 1

)

)

That's all, any help on this would be much appreciated.

Thanks for your time!

[Anonymous · Accepted solution]

I have found the solution for this, if anyone is interested -> https://www.youtube.com/watch?v=tONOosN1xAs

The model structure is: 

The measure is:

#Candidates All Lan =

VAR NumOfSelectedLanguages = COUNTROWS( VALUES('Language'[Language]) )

VAR CandidatesAndLanguages =

SUMMARIZE('Talent Pool','Talent Pool'[ID],'Language'[Language])

VAR CandidatesWithNumLanguages =

GROUPBY(

CandidatesAndLanguages,

'Talent Pool'[ID],

"@Languages", SUMX( CURRENTGROUP(), 1)

)

VAR CandidatesWithAllLanguages =

FILTER( CandidatesWithNumLanguages, [@Languages] = NumOfSelectedLanguages )

RETURN

COUNTROWS( CandidatesWithAllLanguages )

[parry2k]

@Anonymous check this video on my youtube channel and see if it helps.https://youtu.be/zS1IDl3DLak

✨ Follow us on LinkedIn and  to our YouTube channel

Check my latest video on Filters and Sparklines https://youtu.be/wmwcX8HvNxc

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[Anonymous]

Hi, thanks for the quick reply.

I have tried it using your video but it does not fully work as it is not the exact same problem.

I need the count in the KPI card to be correct, so if we select English and French we would obtain 2 distinct IDs who speak both English and French. 

This is the last DAX measure I obtained from following your video: 

Selected Language Candidate v5 =

VAR _selectedLanguage = VALUES('Language'[Language])

VAR _selectedLanguageCandidate =

CALCULATETABLE(

VALUES('User Language'[ID]),

ALL('User Language'),

TREATAS(_selectedLanguage,'Language Calculated'[Language])

)

RETURN

CALCULATE(

DISTINCTCOUNT('User Language'[ID]),

TREATAS(_selectedLanguageCandidate,'User Language'[ID])

)

(using separated Language table for the slicer)