Measure - Count of a column value based on Max Date

[TCavins]

I have a table with the following columns:

ID

StatusDate

Status

In a measure, I'm trying to summarize and get the maximum status date for each ID and what the status is for that max date.

I then want a count from that summarization where status = 'Pending Status'. Basically, for the last status of each ID, how many have 'Pending Status'.

How can I do this within a measure and not create a brand new table?

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[TCavins]

@Ashish_Mathur @bsheffer 

I'm trying to do this without multiple measures.

var tableVar = 
    ADDCOLUMNS(
        FILTER(
        ALL('MyTable'),[StatusRecord]=CALCULATE(MAX('MyTable'[StatusDate]),ALLEXCEPT('MyTable','Mytable'[ID]))
        ), "Test",1)

RETURN CALCULATE(COUNTROWS(FILTER(tableVar, [Status] = "The status I want")))

When I put this measure on a chart, I get the total count. I put a month/year column on the X axis and every month/year has the same total, which is the total rows in tableVar with the status that I want.

How can I do this?

[Ashish_Mathur]

Hi,

The method that i shared with you is the only one i know.  If that does not suit you, then you may want to wait for someone else's solution.

[bsheffer]

you can create a measure to find the max status date

use it in another measure to find the status for the id for that date

you can return that status or that status only if it matches 'Pending Status'

then you can count the number of rows that have a value for last status