Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Proper Filtering Context for Distinctcounts

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,

Need Help
Tips and Tricks
  • OwenAuger's avatar
    OwenAuger
    8 years ago

    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

6 Replies

  • OwenAuger's avatar
    OwenAuger
    Super User
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago
      Thanks Owen - this works great. Appreciate the help.
  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    8 years ago

    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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi,

     

    Drag Case Number to the visual and write the following measure

     

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

     

    Hope this helps.

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Thank you Ashish.

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        8 years ago

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