Forum Discussion

djs1984's avatar
djs1984
Frequent Visitor
3 years ago

Count All Where A Column Value Is Unique

I'm trying to count how many unique visits there have been. 

 

I have a calculated field where each visit is maked as 1, a cancelled visit is reversed out with a -1. Each visit also has a unique Id. I'm trying to do the following and am getting an extra visit in the sum, and I can see there are two with the same Id.

 

Visits (Measure) = CALCULATE(SUM('Ledger Entries'[Visit]), DISTINCT('Ledger Entries'[Visit ID]))
 
Both the visit and the visit id are in the same table. Basically I want to calculate the sum of all visits, but only for distinct visit id's.
General Comment
Show and Tell

3 Replies

  • PVO3's avatar
    PVO3
    Impactful Individual
    3 years ago

     

    Measure =
VAR _tbl =
SUMMARIZE('Ledger Entries', 'Ledger Entries'[Visit ID], "Visits", SUM('Ledger Entries'Visit]))
RETURN

SUMX(_tbl,[Visits])

     

    Assuming you want to take cancelled visits into consideration

     

    • djs1984's avatar
      djs1984
      Frequent Visitor
      3 years ago

      PVO3 Hi, thanks for the reply. This is giving me the same extra visit with the same visitId twice. Cancelled visits don't matter, because they are -1 they should be cancelled out in the sum. 

      I should have 655 visits, but I'm getting 656 and when I drill-through I see the extra one is coming from a duplicate visitId. I'm not sure why this is showing in the count? Totally confused!

      • PVO3's avatar
        PVO3
        Impactful Individual
        3 years ago

        It appears like the visitid in question isn't fully unique. Check for spaces or blanks in both columns Perhapse this is causing the duplication.