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.
Hello,
I have a table where I am trying to count the total number of distinct IDs whose applications have been accepted.
ID | Application Status | Last Accessed |
101 | Accepted | 8/12/2020 |
101 | Accepted | 8/12/2020 |
101 | Denied | 8/10/2020 |
105 | Accepted | 8/13/2020 |
109 | Denied | 8/13/2020 |
107 | Accepted | 8/13/2020 |
108 | Accepted | 8/12/2020 |
104 | Accepted | 8/12/2013 |
103 | Denied | 8/13/2020 |
103 | Denied | 8/13/2020 |
105 | Denied | 8/12/2020 |
109 | Accepted | 8/12/2020 |
I know how to do the st Date here but I am trying to know how to pull in the status of the ID and count it based on the latest date.
I am trying to count the number of unique IDs that have been accepted at the most recent date.
@supergallager34 , Try a measure like
Measure =
VAR __id = MAX ( 'Table'[ID] )
VAR __date = CALCULATE ( MAX( 'Table'[ Last Accessed] ), ALLSELECTED ( 'Table' ), 'Table'[ID] = __id )
RETURN CALCULATE ( Max ( 'Table'[Last Accessed] ), VALUES ( 'Table'[ID ), 'Table'[ID] = __id, 'Table'[Last Accessed] = __date )
or
Measure =
VAR __id = MAX ( 'Table'[ID] )
VAR __date = CALCULATE ( MAX( 'Table'[ Last Accessed] ), ALLSELECTED ( 'Table' ), 'Table'[ID] = __id )
RETURN CALCULATE ( Max ( 'Table'[Application Status] ), VALUES ( 'Table'[ID ), 'Table'[ID] = __id, 'Table'[Last Accessed] = __date )
or
last status = lastnonblankvalue('Table'[Last Accessed] ,Max ( 'Table'[Application Status] ))
take Id and above measure and last accessed date max
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 |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
33 | |
18 | |
18 |