Count All Where A Column Value Is Unique

[djs1984]

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.

[PVO3]

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]

@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]

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.