topic Re: The power bi does not calculate identities in different rows in DAX Commands and Tips

The power bi does not calculate identities in different rows

hana123 — Tue, 03 Jan 2023 08:38:03 GMT

hi everyone

this is the my table

ID isDiagnosisDiabetes isDiagnosisHeartFailure isDiagnosisHypertension Date

1 Yes No No 1/1/2022

1 No No Yes 2/7/2022

i would like to Count any patient with more than one diseases

but power bi in this case will return 0 id

when should return 1

becuse patient number 1 has Diabetes and hypertension (more than one diseases ) but becuse it happend in different dates (rows)

power bi will not count it !

how can i Solve this problem?

THANK YOU

Re: The power bi does not calculate identities in different rows

AlB — Tue, 03 Jan 2023 09:16:55 GMT

Hi hana123

See it all at work in the attached file.

Best to unpivot the columns to get a more manageable table structure first.

Then you can create a measure for the number of patients:

MoreThan1DiseaseFlag = VAR aux_ = CALCULATE ( DISTINCTCOUNT ( Table1[Diagnosis] ), Table1[Result] = "Yes" ) > 1 RETURN IF ( aux_, 1, 0 )

that uses this other measure:

NumPatientsWithMoreThan1Disease = SUMX ( DISTINCT ( Table1[ID] ), [MoreThan1DiseaseFlag] )

You can also use [MoreThan1DiseaseFlag] to show in a table visual the list of patients with more than one disease, by using the measure as a table visual and selecting it to show when the value is 1

Please accept the solution when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Re: The power bi does not calculate identities in different rows

FreemanZ — Tue, 03 Jan 2023 12:46:50 GMT

hi hana123

you may create a measure like this:

PatientsWithMultiDisease = SUMX( VALUES(data[ID]), VAR _db = CALCULATE(COUNTROWS(data), data[IsDB]="Yes") VAR _hf = CALCULATE(COUNTROWS(data), data[IsHF]="Yes") VAR _ht = CALCULATE(COUNTROWS(data), data[IsHT]="Yes") RETURN IF(_db+_hf+_ht>1, 1, 0) )

i tried to expand your data sample:

and verified the measure with a card visual: