Count of Patients group by status

[ssr80]

Hi ALL,

i am having a tricky situation ,wher i need to count No of patients for each smoking status.

i know this is bit confusing. please ask me questions and i can clarify.

scenario:

A patient can visit multiple practices and have different patient ID, but the Link id is unique for each patient.

Here, I need to count the number of unique patients under each smoking type

The condition is

Ex1:

If a patient has smoking status as Nothing recorded at 1 practice

And the same patient has smoking status as daily smoker at different practice. He should be counted under Daily smoker.

Ex2:

if the same patient visited the 3rd practice and recorded his smoking status as Ex smoker. then consider the status recorded on the Latest review date. (if the status on the last review date is Nothing recorded only, check the status of the previous review date)

only if the patient smoking status is Nothing Recorded at all practices then he can be counted under Nothing Recorded.

 

any help would be appreciated.

Regards

SS

[v-shex-msft]

Hi @ssr80,

You can try to use my sample file if it suitable for your requirement.

Notice: I add two temp tables to store summary records.

Regards,

Xiaoxin Sheng

[ssr80]

Hi Xiaoxin Sheng,

Thanks for your reply. 

i am basically struck with the following scenario.

can you please help me with the following requirement.

my result should look like the table that is on the right.

the requirement here is:

if the patient has latest smoking status other than Blank or 9, then consider that as the smoking status of the patient.

But,

if a patient has latest smoking status as blank or 9, then we need to consider the previous smoking status that is not blank or 9.

in case, if the smoking status is blank or 9 in all the dates, then we consider the smoking status as 9.

regards

SS

[Ashish_Mathur]

Hi,

You may refer to my solutino at this link.

Hope this helps.

[ssr80]

@Ashish_Mathur

hi ashish, that works fine. 

But, how can i do that in power BI.

Thanks 

SSR80

[Ashish_Mathur]

Hi,

Download the file from here.

Hope this helps.

[ssr80]

@Ashish_Mathur

thanks for your quick reply. 

can you please help me to derive the Result column, in the below table.

i actually need the smoking status grouped by month.

if single patient in a month has multiple smoking status recorded. consider the latest one that is not 9 and display in the result column.

or

the other option is to create a new table with patinentID, review month, final smoking status.

Also, please suggest which would be the best option.

ID	patientID	review date	review month	smoking status	Result Column
1	1	12/01/2018	Jan-18	9	1
2	1	10/01/2018	Jan-18	1	1
3	1	5/01/2018	Jan-18	5	1
4	1	20/12/2017	Dec-17	3	3
5	1	15/12/2017	Dec-17	1	3
6	2	11/01/2018	Jan-18	9	9
7	2	10/01/2018	Jan-18	9	9
8	2	9/02/2018	Feb-18	2	2
9	2	7/02/2018	Feb-18	5	2
10	3	12/02/2018	Feb-18	9	5
11	3	10/02/2018	Feb-18	5	5
12	3	5/12/2017	Dec-17	9	3
13	3	20/12/2017	Dec-17	3	3
14	3	15/12/2017	Dec-17	3	3

Thanks in advance.

Regards

SSR

[Ashish_Mathur]

Hi,

What if you drag months in the visual in my solution.  Do you not get the desired result?

[ssr80]

@Ashish_Mathur

Hi ashish, your solution works well with the sample data provided. 

But, when i do the same on my actual data. it is giving me error when i drag the measure "Smoking statuss on that date"

error: 

A date column containing duplicate dates was specified in the call to function 'DATESBETWEEN'. this is not supported.

Regards

SSR

[Ashish_Mathur]

Hi,

I'll need to see your file.

[ChandeepChhabra]

@ssr80,

Please make the following measure

Status =

Var MaxDate =CALCULATE(MAX(Patients[Review Date]),Patients[Smoking Status]<>9&&Patients[Smoking Status]<>BLANK()) RETURN IF(COUNTROWS(Patients)=CALCULATE(COUNTROWS(Patients),Patients[Smoking Status]=9||Patients[Smoking Status]=BLANK()),9,CALCULATE(sum(Patients[Smoking Status]),FILTER(Patients,Patients[Review Date]=MaxDate)))

Download the pbi file

[ssr80]

@ChandeepChhabra

Hi Chandeep,

Thanks for the reply. 

this is not giving me the right result. i can see you are summing up all the status that is not 9 or Blank.

what i am expecting is, if the latest date has status 9 or Blank. then i need status recorded on the previous date.

Regards

SSR80

[v-shex-msft]

HI @ssr80,

You can try to use following formula:

Table =
VAR summytable =
    SUMMARIZE (
        FILTER ( LastStatus, NOT ( [Smokin Status] IN { BLANK (), 9 } ) ),
        [PatientID],
        "ReviewDate", LASTDATE ( LastStatus[ReviewDate] )
    )
VAR singleStatus =
    CALCULATETABLE (
        LastStatus,
        FILTER (
            ALL ( LastStatus ),
            COUNTROWS (
                CALCULATETABLE (
                    VALUES ( LastStatus[Smokin Status] ),
                    FILTER ( ALL ( LastStatus ), [PatientID] = EARLIER ( [PatientID] ) )
                )
            )
                <= 1
        )
    )
VAR summytable2 =
    SUMMARIZE (
        singleStatus,
        [PatientID],
        "ReviewDate", MAXX (
            FILTER ( singleStatus, [PatientID] = EARLIER ( [PatientID] ) ),
            [ReviewDate]
        ),
        "Smokin Status", MAXX (
            FILTER ( singleStatus, [PatientID] = EARLIER ( [PatientID] ) ),
            [Smokin Status]
        )
    )
RETURN
    UNION (
        ADDCOLUMNS (
            summytable,
            "Smokin Status", LOOKUPVALUE (
                LastStatus[Smokin Status],
                [PatientID], [PatientID],
                [ReviewDate], [ReviewDate]
            )
        ),
        summytable2
    )

In my opinion, I'm not so sure why you persist to use power bi formulas to calculate with complex conditions, it is low performance and hard to coding formula. I'd like to suggest you calculate on excel side, then import to power bi.

Regards,

Xiaoxin Sheng