Flag when a user has a specific status update

[pbimhkc]

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

[amitchandak]

@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])

[PaulOlding · Accepted 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: