Generate MaxDate within a group and assess a condition

[sperry]

I have a problem I thought was easy to solve but having real issues in PowerBI.

I have a table of which contains many measures for instances of care. (See sample table below).

I want to answer the simple question of how many are active in the service now. I can ascertain this by taking the MaxDate of the MesDate for each EncryptedNHI and if the Discharged column is null then the individual is active in the service. I know what the sql would kinda look like but I cannot create a measure or even a table in query editor that will generate what I need.

Any suggestions?

MESDATE	ENCRYPTEDNHI	DISCHARGED29
2/07/2018 15:20	A9999	null
3/07/2018 10:16	A27775	null
3/07/2018 11:10	A15554	null
4/07/2018 10:24	A15554	null
5/07/2018 11:20	A14443	null
9/07/2018 10:21	A7777	null
9/07/2018 12:20	A21109	null
10/07/2018 15:01	A33330	null
10/07/2018 15:28	A36663	null
11/07/2018 9:08	A12221	null
11/07/2018 8:45	A33330	null
11/07/2018 9:04	A12221	null
27/06/2018 9:04	A12221	null
11/07/2018 9:59	A5555	null
11/07/2018 16:24	A25553	null
16/07/2018 11:18	A28886	null
16/07/2018 15:02	A13332	null
16/07/2018 16:28	A16665	null
19/06/2018 16:29	A16665	null
12/07/2018 16:32	A16665	null
16/07/2018 16:36	A16665	Ticked
17/07/2018 11:06	A12221	null
17/07/2018 11:46	A18887	null
17/07/2018 14:34	A4444	null
9/07/2018 9:08	A14443	null
17/07/2018 9:15	A14443	null
18/07/2018 10:24	A29997	null
18/07/2018 10:32	A26664	null
19/07/2018 10:50	A23331	null
19/07/2018 11:30	A14443	null
19/07/2018 11:33	A25553	null
19/07/2018 15:30	A31108	null
19/07/2018 16:00	A9999	null
23/07/2018 11:33	A19998	null

[Greg_Deckler]

Put ENCRYPTEDNHI in a table visualization.

Create this measure:

IsActive =
VAR __maxDate = MAX([MESDATE])
VAR __status = MAXX(FILTER('Table',[MESDATE]=__maxDate),[DISCHARGE29]
RETURN
IF(ISBLANK(__status),TRUE,FALSE)

Put that in your table visual as well.

[sperry]

Wow thatnks for getting back so quickly..

A couple of things;

- I get the following error message when generating a measure

IsActive =
VAR __maxDate = MAX(MMNOutcome[MESDATE])
VAR __status = MAXX(FILTER(MMNOutcome,MMNOutcome[MESDATE]=__maxDate),MMNOutcome[DISCHARGED29]
RETURN
IF(ISBLANK(_status),TRUE,FALSE)

The syntax for 'RETURN' is incorrect. (DAX(VAR __maxDate = MAX(MMNOutcome[MESDATE])VAR __status = MAXX(FILTER(MMNOutcome,MMNOutcome[MESDATE]=__maxDate),MMNOutcome[DISCHARGED29]RETURNIF(ISBLANK(__status),TRUE,FALSE))).

- The second is that I would like to generate a measure that just counts the active status. Or maybe do I need to generate a table in the query editor then generate a measure over that?

[Greg_Deckler · Accepted solution]

My bad, I missed a paren, that's the danger of doing this stuff on the fly versus testing it!

IsActive =
VAR __maxDate = MAX(MMNOutcome[MESDATE])
VAR __status = MAXX(FILTER(MMNOutcome,MMNOutcome[MESDATE]=__maxDate),MMNOutcome[DISCHARGED29])
RETURN
IF(ISBLANK(_status),TRUE,FALSE)

Hmmm, if you want a count, then you will need to do something like:

# Active = 
VAR __tmpTable = ADDCOLUMNS(MMNOutcome,"__IsLatest",IF([MESDATE]=MAXX(FILTER(ALL(MMNOutcome),[ENCRYPTEDNHI]=EARLIER(MMNOutcome[ENCRYPTEDNHI])),[MESDATE]),TRUE,FALSE))
RETURN COUNTROWS(FILTER(__tmpTable,[__IsLatest] && MMNOutcome[DISCHARGED29]="null"))

Not sure about your data. When I entered it via Enter Data query, I got "null" in for the DISCHARGED29. If your data is actual nulls, as in nothing then you will have to change the last condition in the RETURN statement to something like ISBLANK(MMNOutcome[DISCHARGED29]) instead of MMNOutcome[DISCHARGED29]="null"

[sperry]

Perfect! This will come in super handy for many things..thanks so much for your support