Need Help on Cross metrix

[RanjanThammaiah]

Hi All,

Can some one help me to get the desired result. Not sure if the formula is incorrect.

Data

Classification	IdPerson
A	1
A	2
B	1
B	2
B	3
C	2
C	3

Classification

A

B

C

Classification_dup

A

B

C

Result should be

Classified	A	B	C
A	0	2	1
B	2	0	2
C	1	2	0

But in Power Bi its not coming as expected.

People = DISTINCTCOUNT(Data[IdPerson])

Measure = if(VALUES(Classification[Classification])=VALUES(Classification_dup[Classification]),0,CALCULATE([People],CALCULATETABLE(SUMMARIZE(VALUES(Data[IdPerson]),Data[IdPerson]),ALL(Classification),USERELATIONSHIP(Data[Classification],Classification_dup[Classification]))))

[v-yiruan-msft · Accepted solution]

Hi @RanjanThammaiah ,

You can create a measure as below:

Count of Person = 
var _t1=CALCULATETABLE(VALUES('Data'[IdPerson]),FILTER(all('Data'),Data[Classification]=MAX('Classification'[Classification])))
var _t2=CALCULATETABLE(VALUES('Data'[IdPerson]),FILTER(all('Data'),Data[Classification]=MAX('Classification_dup'[Classification_dup])))
var _t3=INTERSECT(_t1,_t2)
return if(MAX('Classification'[Classification])=MAX('Classification_dup'[Classification_dup]),0,COUNTROWS(_t3))

Best Regards

Rena