Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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"
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.
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?
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"
Perfect! This will come in super handy for many things..thanks so much for your support
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
96 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |