topic Re: Count, for 2 different keys, the number of shared values in DAX Commands and Tips

Count, for 2 different keys, the number of shared values

Anonymous — Thu, 05 Mar 2020 13:51:25 GMT

hello,

I have a table like this :

Key	Value
Store 1	Apple
Store 1	Orange
Store 2	Apple
Store 2	Banana
Store 2	Orange
Store 3	Banana
Store 3	Orange

I would like to display a card where, when I select 2 stores, I get the number of values they have in common.

I tought of creating a table like that :

key 1	key 2	(Calculated column) Nb shared values
Store 1	Store 2	2
Store 1	Store 3	1
Store 2	Store 1	2
Store 2	Store 3	1
Store 3	Store 1	1
Store 3	Store 2	1

That way I can filter on key 1 and key 2 and display the Nb shared values.

Is it the right way to do it ? If yes, how do I compute the "Nb shared values" column with DAX ?

Thanks in advance for your help.

Re: Count, for 2 different keys, the number of shared values

Greg_Deckler — Thu, 05 Mar 2020 13:55:35 GMT

Perhaps my In Common quick measure: https://community.powerbi.com/t5/Quick-Measures-Gallery/In-Common/m-p/382956#M117

Re: Count, for 2 different keys, the number of shared values

Anonymous — Thu, 05 Mar 2020 14:13:46 GMT

Hi Greg,

Thanks for your quick answer !

It looks like it coud be the solution 🙂 I will test it.

But can you please explain to us how did you construct your measure ? I don't get it ...

InCommon = 
VAR students1 = FILTER(ALL(Students),Students[Teacher]=MAX(Teachers[Teacher]))
VAR students1a = SELECTCOLUMNS(students1,"MyStudent",[Student])
VAR students2 = FILTER(ALL(Students),Students[Teacher]=MAX(Teachers2[Teacher]))
VAR students2a = SELECTCOLUMNS(students2,"MyStudent",[Student])
VAR incommon = COUNTROWS(INTERSECT(students1a,students2a))
RETURN IF(ISBLANK(incommon),0,incommon)

Re: Count, for 2 different keys, the number of shared values

Anonymous — Sat, 07 Mar 2020 22:49:31 GMT

You have to have 2 tables with unique Stores only so that you can select 2 stores independently.

"Store 1" Table:

Key

Store 1

Store 2

Store 3

...

"Store 2" Table:

Key

Store 1

Store 2

Store 3

...

These tables will not be connected to the table that holds Key and Values. If one store is selected from Store 1 and one is selected from Store 2, then here's a measure that will calculate the number of values they have in common:

[# Values In Common] = var __store1 = selectedvalue( 'Store 1'[Key] ) var __store2 = selectedvalue( 'Store 2'[Key] ) var __values1 = CALCULATETABLE( DISTINCT( StoresWithValues[Value] ), treatas( __store1, StoresWithValues[Key] ) ) var __values2 = CALCULATETABLE( DISTINCT( StoresWithValues[Value] ), treatas( __store1, StoresWithValues[Key] ) ) var __result = COUNTROWS( INTERSECT( __values1, __values2 ) ) return __result

Best