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...
Need Help
Tips and Tricks
  • 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.

Anonymous's avatar
Anonymous
Not applicable
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.