The power bi does not calculate identities in different rows

[hana123]

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 

[AlB · Accepted solution]

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.

[FreemanZ · Accepted solution]

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:

[AlB · Accepted solution]

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.