Need Help in calculating distinct count using multiple conditions

[mandeeps19]

Hi,

Need help in calculating distinct count of App where tickets status is approved but those should include only tickets where you have one Approved status.

so for example  if app A is have both approved status and inflight status it should not count but only count if it is Approved status

Approved statuses  - 

Approved

Approved With Exceptions
Conditionally Approved

and rest all in flight status

APP	Ticket	Status
A	T-0001	Discarded
A	T-0002	New Request
A	T-0003	Pending DA Review
B	T-0004	Rejected
B	T-0005	Approved
B	T-0006	Approved With Exceptions
B	T-0007	Conditionally Approved
C	T-0008	Pending E Review
D	T-0009	Pending Findings - D/APP TEAM
D	T-0010	A Scheduled
E	T-0011	Pending Findings – E
E	T-0012	C Scheduled
E	T-0013	E SCHEDULED
E	T-0014	Discarded
E	T-0015	New Request
E	T-0016	Pending DA Review
F	T-0017	Rejected
F	T-0018	Approved
F	T-0019	Approved With Exceptions
F	T-0020	Conditionally Approved
G	T-0021	Pending EA Review
G	T-0022	Pending Findings - D/APP TEAM
G	T-0023	A Scheduled
G	T-0024	Pending Findings – E
G	T-0025	C Scheduled
G	T-0026	E SCHEDULED
G	T-0027	Approved

@amitchandak 

[v-rzhou-msft]

Hi @mandeeps19 ,

I suggest you to try code as below to create a measure. I think the result should be 0.

Distinct Count APP only in Approved Status =
CALCULATE ( DISTINCTCOUNT ( 'Table'[APP] ) )
    - CALCULATE (
        DISTINCTCOUNT ( 'Table'[APP] ),
        FILTER (
            'Table',
            NOT ( 'Table'[Status] IN { "Approved With Exceptions", "Conditionally Approved" } )
        )
    )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mandeeps19]

The result should be 4 as you can see A,C,D & E doesnt have the approved status