Proper Filtering Context for Distinctcounts

[Anonymous]

Hello,

I have a table with a handful of columns that offer information about the transactional history of a case. The Case Number, the Time of Entry into Queue and AssignTime are the primary columns of interest. There are instances when a case may enter the queue at different times throughout its lifecycle and instances in which, despite an attempt to assign the case, it never gets assigned from the queue. The latter will result in a null value within the AssignTime column:

Case Number                   Time of Entry                   AssignTime

12345                                1:02:34 AM

12345                                1:02:34 AM                      3:45:13 AM

12345                                7:45:20 PM                                                   

23456                                6:20:58 AM                     

23456                                7:17:23 AM

23456                                7:17:23 AM

34567                                3:10:45 PM                      

34567                                3:10:45 PM                       4:20:14 PM

98765                                5:13:13 AM       

In summary, there are 4 distinct cases and I want to show that 2 of the 4 were never assigned. I need to keep the rows that do not have an Assigntime indication as they include other information that is included in different measures within the report. Any help is appreciated.

Thank you,

[Ashish_Mathur]

Hi,

Drag Case Number to the visual and write the following measure

=if(COUNTBLANK(Data[AssignTime])=COUNTROWS(Data),1,BLANK())

Hope this helps.

[Anonymous]

Thank you Ashish.

[Ashish_Mathur]

You are welcome.  If my reply helped, please mark it as Answer.

[OwenAuger · Accepted solution]

@Anonymous

Here is a simple measure that should do what you want:

Cases Never Assigned =
COUNTROWS (
    FILTER (
        VALUES ( YourTable[Case Number] ),
        ISBLANK ( CALCULATE ( MAX ( YourTable[AssignTime] ) ) )
    )
)

The measure counts the number of Case Numbers where the maximum AssignTime is blank, indicating that there is no AssignTime.

You could possibly do something with SELECTEDVALUE but I think this measure is good enough.

Regards,

Owen

[Anonymous]

Thanks Owen - this works great. Appreciate the help.

[v-piga-msft]

Hi @Anonymous,

---

In summary, there are 4 distinct cases and I want to show that 2 of the 4 were never assigned. I need to keep the rows that do not have an Assigntime indication as they include other information that is included in different measures within the report. Any help is appreciated.

---

I would appreciated if you could share your expected output, so that we can help further investigate on it?

Best Regards,

Cherry