Forum Discussion

njxfoster's avatar
njxfoster
Helper I
4 years ago

Distinct count with date parameter

Hi Guys

 

I want to create a distinct count for cancel note

 

These "Request from Management - pick up resource hours" should only be count once since it's all from the same date but different shift.

 

However, If I only use distinct count in this situation. "Request from Management - pick up resource hours" from other date will all count as only one note in total

 

 

Is there anyway I can count this cancel note also with parameter of different date so "Request from Management - pick up resource hours" from other date will count seperatly?

 

 

Thanks In advance.

4 Replies

  • Whitewater100's avatar
    Whitewater100
    Solution Sage
    4 years ago

    Hello:

    What is your expected result?

    Can you try for the first measure:

    Cancel Note CT = DISTINCTCOUNTNOBLANK(Count_Table[Cancel_Note])
     
    Then to Count the days :
    Count Measure = SUMX(
    VALUES(Count_Table[shift]),
    [Cancel Note CT])
     

     

    • njxfoster's avatar
      njxfoster
      Helper I
      4 years ago

      Hi! Thank you for your answer

       

      For example:

      If I use distinctcountnoblank. the result for "request from managemnt" will only show 1 

       

      I want to add condition to the measure so it count this "request from management" as 3 since there are three different agents who share same cancel reason.

       

       

      • Whitewater100's avatar
        Whitewater100
        Solution Sage
        4 years ago

        OK. You can put calc column in table:

        Count =
        var _name = Data[Name]
        var _note = Data[Cancel_Note]
        return
        COUNTX(
        FILTER(
        ALL(Data),
        Data[Cancel_Note] = _note &&
        Data[Name] = _name)
        , Data[Cancel_Note])
         
        Then measure:
        Manager CT = CALCULATE(DISTINCTCOUNT(Data[Name]),
        Data[Count] >0)
         
        Does this work for what you need?
  • v-janeyg-msft's avatar
    v-janeyg-msft
    Community Support
    4 years ago

    Hi,   njxfoster 

    The sample data you share is not representative, Does the cancel_note column have only one value? The same agent_name appears on only one day? Please describe your needs completely without your subjective operational awareness. In this way we can give an accurate formula. 

    Do you want to count how many different dates have cancel_note under the current agent_name?

     

    Best Regards,
    Community Support Team _ Janey