Help with sorting patients acording to diagnosis

[aleph_heideger]

Hi all.

New here so go easy on me.

I'm using PowerBI for the past few months (or at least trying to use) at my workplace, so far with some success.

I'm currently working on a diabetes clinic: multidisciplinary approach where the patients can be seen by a number of specialists from medical doctors to podologists.

The thing is, at the moment we are preparing next year budget and I though of doing part of the job using PowerBI.

So, to begin:

We have ate the moment more than 2000 patients in the clinic, and each one is diabetic but also as other problems, including differente complications from diabetes.

I have at the moment several excel files:

1. a list of all the patients
2. a list of all the different diagnosis
3. a list of patients per diagnosis (a lot of them)

To be able to work it in PowerBI I imported file 1 and merged all the files in point 3 - something like:

Patient   |   diagnosis

a                 y

b                 y

c                 y

d                 y

a                 x

b                 x

f                  x

b                 w

and so on...

So far so good.

A create a dashboard and it's here the real trouble begins:

I can count how many of my diabetic patients are obese, I can count how many smoke or how many have eye problems. What I'm still unable to do is, for instance, calculate how many have 2, 3 or whatever number os diagnosis I choose from, in common. 

Can someone share some ideas?

Thanks

Alexandre

[v-yulgu-msft · Accepted solution]

Hi @aleph_heideger,

Based on my assumption, the report has a slicer lists all diagnosis, now you want to calculate how many patients have all diagnosis you selected from slicer, right?

If above result meets your requirement, please create measures like below:

NO of diagnosis per patient =
CALCULATE ( COUNT ( patient[Patient] ), ALLSELECTED ( patient[diagnosis] ) )

Select number =
CALCULATE (
    DISTINCTCOUNT ( patient[diagnosis] ),
    ALLSELECTED ( patient[diagnosis] ),
    ALLSELECTED ( patient[Patient] )
)

flag =
IF ( [NO of diagnosis per patient] = [Select number], 1, 0 )

NO of patient that have all diagnosis selected =
CALCULATE ( DISTINCTCOUNT ( patient[Patient] ), FILTER ( patient, [flag] = 1 ) )

I have uploaded my pbix file for your reference.

Best regards,
Yuliana Gu

[aleph_heideger · Accepted solution]

An update regarding the proposed solution:

I couldn't make it work on my files: with a small test file the results came out ok but with a more real file items just added up.

I searched through the forums and found a differente approach:

Doentes com todos os diagnosticos =
COUNTROWS (
    EXCEPT (
        VALUES ( DIAGNOSTICOS[Utente] ),
        SUMMARIZE (
            GENERATE (
                VALUES ( DIAGNOSTICOS[Utente] ),
                EXCEPT (
                    VALUES ( DIAGNOSTICOS[ICPC] ),
                    CALCULATETABLE ( VALUES ( DIAGNOSTICOS[ICPC] ) )
                )
            ),
            DIAGNOSTICOS[Utente]
        )
    )
)
    + 0

Assuming:

DIAGNOSTICOS is the name of the table

Utente is the patient name/reference

ICPC is the diagnosis code

[v-yulgu-msft · Accepted solution]

Hi @aleph_heideger,

Based on my assumption, the report has a slicer lists all diagnosis, now you want to calculate how many patients have all diagnosis you selected from slicer, right?

If above result meets your requirement, please create measures like below:

NO of diagnosis per patient =
CALCULATE ( COUNT ( patient[Patient] ), ALLSELECTED ( patient[diagnosis] ) )

Select number =
CALCULATE (
    DISTINCTCOUNT ( patient[diagnosis] ),
    ALLSELECTED ( patient[diagnosis] ),
    ALLSELECTED ( patient[Patient] )
)

flag =
IF ( [NO of diagnosis per patient] = [Select number], 1, 0 )

NO of patient that have all diagnosis selected =
CALCULATE ( DISTINCTCOUNT ( patient[Patient] ), FILTER ( patient, [flag] = 1 ) )

I have uploaded my pbix file for your reference.

Best regards,
Yuliana Gu

[aleph_heideger]

I think that may do the trick.

I tried on my old database but could't replicate the results. Maybe some noise from other measures, I don't know.

EDIT:

I tried with a clean excel file and was able to get exactly what I wanted. Now I'll try to debug why the heck it didn't work in my first file.

Many thanks.

[v-yulgu-msft]

Hi @aleph_heideger,

If you work it out, appreciate that you could share the valid solution.

Thanks,

Yuliana Gu