Forum Discussion

Hell-1931's avatar
Hell-1931
Helper II
3 years ago
Solved

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:

 

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!

 

 

 

  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    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.