cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Hell-1931
Helper I
Helper I

Calculating values for Cross Matrix table in Power BI DAX

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

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

Diagnosis:

Diagnosis.PNG

 

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:

Cat_Per_Clnt_PBI.PNG

 

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:

CrossMatrix.PNG

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!

 

 

 

1 ACCEPTED SOLUTION
RicoZhou
Community Support
Community Support

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.

RicoZhou_1-1664527492885.png

 

 

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.

View solution in original post

4 REPLIES 4
Hell-1931
Helper I
Helper I

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

Hell-1931
Helper I
Helper I

Thank you, I'll try tonight

RicoZhou
Community Support
Community Support

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.

RicoZhou_1-1664527492885.png

 

 

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.

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

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors