Need to calculate count based on last status

[yashwant101]

Hi all,

I am creating a chart where I need to take the count of patients whose last status was active.

Below is the screenshot of the data

I have created below measures to get the latest status and then count

Last Status =

VAR _Max_Dt = SUMX(SUMMARIZE('Table','Table'[Patient], "Max Dt", MAX('Table'[Dt])), [Max Dt])

VAR _MaxStat = LOOKUPVALUE('Table'[Stat], 'Table'[Dt], _Max_Dt)

RETURN

_MaxStat

Total Patient =

CALCULATE(DISTINCTCOUNT('Table'[Patient]), ALL('Table'[Dt]), 'Table'[Dt]<= MAX('Table'[Dt]), [Last Status] = "Active")

The last status one works fine but when I try to calculate total patients, I am not able to put the last status measure in the dax.

Any help is highly appreciated.

Regrads,

Yashwant Mishra

[Anonymous · Accepted solution]

Hi @yashwant101 ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a measure. 

Total Patient =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Patient] ),
    FILTER (
        SUMMARIZE ( 'Table', [Patient], "last status", [Last Status] ),
        [last status] = "Active"
    )
)

(3) Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

[yashwant101]

Hi @Ashish_Mathur @Anonymous ,

Thanks for your super helful replies. One thing I missed in the requirement. The patient whose last status was active on any date prior to the last date os selection should also be counted. Eg. In the ss below P2 & P3 both should be counted in the given selection as last status of P2 was active even though prior to the selection:

But the last date selection should be maitained.

Regards,

Yashwant

[Ashish_Mathur]

Your requirement is confusion and sharing a screenshot is useless.  Share some data to work with and show the expected result.

[Anonymous]

Hi @yashwant101 ,

We can create a table.

Table 2 = VALUES('Table'[Patient])

Then we can update the Total Patient measure.

Total Patient = 
var _table=SUMMARIZE ( ALL('Table'), [Patient], "last status", [Last Status] )
return COUNTROWS(FILTER(_table,[last status]="Active" && [Patient] in VALUES('Table 2'[Patient])))

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[Anonymous · Accepted solution]

Hi @yashwant101 ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a measure. 

Total Patient =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Patient] ),
    FILTER (
        SUMMARIZE ( 'Table', [Patient], "last status", [Last Status] ),
        [last status] = "Active"
    )
)

(3) Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.