Forum Discussion

adoster's avatar
adoster
Resolver I
4 years ago
Solved

Help with DistinctCount by Date

Trying to find the distinct count of procedures by date.

 

DateNameProcedure Display NameDistinct PDN
11/8/2021Mickey MouseProcedure A1
11/8/2021Mickey MouseProcedure B1
11/9/2021Mickey MouseProcedure A1
11/9/2021Mickey MouseProcedure B1

 

Desired Result:

DateDistinct Count of PDN
11/8/20211
11/9/20211
Total2

 

I tried a simple measure doing a discount count on the NAME which returns a value of 1

Measure1 = DISTINCTCOUNT('Table'[Name])

 

 

Desired Results with Multiple People across 3 days

DateNameProcedure Display NameDistinct PDN
11/8/2021Donald DuckProcedure A1
11/8/2021Donald DuckProcedure B1
11/8/2021Mickey MouseProcedure A1
11/8/2021Mickey MouseProcedure B1
11/9/2021Donald DuckProcedure A1
11/9/2021Donald DuckProcedure B1
11/9/2021Mickey MouseProcedure A1
11/10/2021Mickey MouseProcedure A1
  TOTAL5

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi adoster 

    Try to create this measure to achieve your goal.

    Measure = 
VAR _SUMMARIZE = SUMMARIZE('Table','Table'[Date],"DISTINCT COUNT",DISTINCTCOUNT('Table'[Name]))
RETURN
SUMX(FILTER(_SUMMARIZE,[Date]<= MAX('Table'[Date])),[DISTINCT COUNT])

    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.

5 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    You need to decide what you are actually trying to measure. Distint count of name, distinct count of procedure, or distinct count of [Name]+[Procedure]  ?

  • adoster's avatar
    adoster
    Resolver I
    4 years ago

    I believe that I need distinct count based on Name & Procedure.

    If one Name has 5 procedures on 1 day = 1 volume

    If two Names each have 5 procedures on 1 day = 2 volume

    If two Names each have 5 procedures on 2 days = 4 volume

    If three Names each have 5 procedures on 2 days = 6 volume

     

    In the first example above I did a Distinct Count on Name. The Total count give me 1 instead of the desired 2

    Hope this helps

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi adoster 

      Try to create this measure to achieve your goal.

      Measure = 
VAR _SUMMARIZE = SUMMARIZE('Table','Table'[Date],"DISTINCT COUNT",DISTINCTCOUNT('Table'[Name]))
RETURN
SUMX(FILTER(_SUMMARIZE,[Date]<= MAX('Table'[Date])),[DISTINCT COUNT])

      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.