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

[Anonymous]

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.

[Anonymous]

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

D

[Greg_Deckler]

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

[Anonymous]

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)