Calculating values for Cross Matrix table in Power BI DAX

[Hell-1931]

I have the following table, which shows how many MH(Mental Health type)  - 

SA (Substance Abuse type) diagnosis for a particular client:

Diagnosis:

I also have a measure, which calculates number of  Diagnosis categories - SA/MH per client:

So, if I populate my data with [# Category per Client] measure, it'll look as:

My goal is to create a Cross Matrix table, where

X axis = SA diagnosis,

Y axis = MH diagnosis,

value = # of Clients that have a SA-MH diagnosis pair (crossed pair).

This is the measure for my value:

# Dual Diagnosed Clients = 
  CALCULATE(DISTINCTCOUNT('Diagnosis'[ClientID]),
              FILTER('Diagnosis',
                     'Diagnosis'[# Category per Client] = 2
                               ))
                                  

I created 2 tables with the unduplicated MH / SA categories - Attribute_SA [Attrib_1],  Attribute_MH[Attrib_2]

My Cross Matrix table, with [# Dual Diagnosed Clients] measure is below:

But, unfortunately, it shows the total # of the unduplicated Dually Diagnosed (SA/MH) clients (ID 22,24,25) instead of showing - 

# of the unduplicated Dually Diagnosed (SA/MH) clients for each SA-MH category.

I expect to see the following numbers in my matrix:

Alcohol-Bi-Polar = 1 Client (ClientID 22);

Alcohol-PTSD = 0 Clients (or just an empty cell)

Alcohol-Scizophrenia = 1 Client (ClientID 24)

Alcohol-Stress = 0 Clients

Cocaine-Bi-Polar = 0 Clients 

Cocaine has no dually diagnosed clients - all 0 or empty in that row

Nicotine-Bi-Polar = 0 Clients

Nicotine-PTSD = 1 Client

Nicotine-Stress = 1 Client

Please HELP  or advice how should I change / update my emasure(s)

Thx in advance!

[Hell-1931]

Thank you so much
I'll try and will accept in 2 days!

[Hell-1931]

Thank you, I'll try tonight

[v-rzhou-msft · Accepted solution]

Hi @Hell-1931 ,

Please try this measure.

Measure = 
VAR _ADDCOLUMN = ADDCOLUMNS('Table',"Flag",IF([Diagnosis_Desc] = MAX(MH[Diagnosis_Desc]) || [Diagnosis_Desc] = MAX(SA[Diagnosis_Desc]),1,0))
VAR _SUMMAIZE = SUMMARIZE(_ADDCOLUMN,[ClientID],"Sum",SUMX(FILTER(_ADDCOLUMN,'Table'[ClientID] = EARLIER('Table'[ClientID])),[Flag]))
RETURN
COUNTAX(FILTER(_SUMMAIZE,[Sum]=2),[ClientID]) + 0

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Hell-1931]

Rico Zhou,

Sorry, just to let you know -

Your measure worked perfectly.  

But what if I'd needed 2 more fields in my original dataset -

[ProgramID], [Date]?

In that case the measure works not correctly.  

How should I modify it, so it would work with [ProgramID], [Date] in a dataset?

Here is the link to my post

Cross Matrix table in Power BI DAX - need to count... - Microsoft Power BI Community