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 am building a dashboard to track how many people have been approved/denied and need to get distinct counts of how many people had an approval with no denial, how many people were initially denied then approved, and how many people were denied and not eventually approved. The data I'm working with looks something like this where I have one approval status and a handful of different statuses that map to "denied". Is there a way to get the results I want through dax measures or calculated columns?
person_id | status | status update |
1 | In Progress | 5/1/22 |
1 | Sent | 5/2/22 |
2 | In Progress | 5/2/22 |
3 | In Progress | 5/1/22 |
3 | Sent | 5/2/22 |
3 | Denied | 5/3/22 |
3 | Appealed | 5/4/22 |
3 | Approved | 5/5/22 |
4 | Sent | 5/4/22 |
4 | Administrative Denial | 5/5/22 |
Solved! Go to Solution.
Hi @pbimhkc
One approach you could take to this is measures that create virtual tables of person_ids that have been Approved and person_ids that have been Denied, then use INTERSECT and EXCEPT to answer your questions.
So, to build the virtual tables you'd use this code:
VAR _Approved =
CALCULATETABLE(
VALUES('Table'[person_id]),
'Table'[status] = "Approved"
)
VAR _Denied =
CALCULATETABLE(
VALUES('Table'[person_id]),
'Table'[status] IN {"Denied", "Administrative Denial"}
)
To find the person_ids that are both Approved and Denied we'd use INTERSECT(_Approved, _Denied).
To find the person_ids that are Denied but not approved that would be EXCEPT(_Denied, _Approved).
Putting that together for a Approved and Denied measure we get:
Approved & Denied =
VAR _Approved =
CALCULATETABLE(
VALUES('Table'[person_id]),
'Table'[status] = "Approved"
)
VAR _Denied =
CALCULATETABLE(
VALUES('Table'[person_id]),
'Table'[status] IN {"Denied", "Administrative Denial"}
)
VAR _Result = COUNTROWS(INTERSECT(_Approved, _Denied))
RETURN
_Result
The result:
Hi @pbimhkc
One approach you could take to this is measures that create virtual tables of person_ids that have been Approved and person_ids that have been Denied, then use INTERSECT and EXCEPT to answer your questions.
So, to build the virtual tables you'd use this code:
VAR _Approved =
CALCULATETABLE(
VALUES('Table'[person_id]),
'Table'[status] = "Approved"
)
VAR _Denied =
CALCULATETABLE(
VALUES('Table'[person_id]),
'Table'[status] IN {"Denied", "Administrative Denial"}
)
To find the person_ids that are both Approved and Denied we'd use INTERSECT(_Approved, _Denied).
To find the person_ids that are Denied but not approved that would be EXCEPT(_Denied, _Approved).
Putting that together for a Approved and Denied measure we get:
Approved & Denied =
VAR _Approved =
CALCULATETABLE(
VALUES('Table'[person_id]),
'Table'[status] = "Approved"
)
VAR _Denied =
CALCULATETABLE(
VALUES('Table'[person_id]),
'Table'[status] IN {"Denied", "Administrative Denial"}
)
VAR _Result = COUNTROWS(INTERSECT(_Approved, _Denied))
RETURN
_Result
The result:
@pbimhkc , refer if these example measures can help
Denied and approved =
Countx(filter(Summarize(Table, [person_id], "_approve", calculate(countrows(Table), Filter(Table, Table[Status] = "Approved"))
, "_denied", calculate(countrows(Table), Filter(Table, Table[Status] = "Denied"))), not(isblank( _approve)) && not(isblank(_denied))), [Person_id])
approved not Denied =
Countx(filter(Summarize(Table, [person_id], "_approve", calculate(countrows(Table), Filter(Table, Table[Status] = "Approved"))
, "_denied", calculate(countrows(Table), Filter(Table, Table[Status] = "Denied"))), not(isblank( _approve)) && (isblank(_denied))), [Person_id])
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 |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |