Forum Discussion
Solved
Returning distinct values using a Measure
I need my Date Table to operate on both the Join and Close dates of an entity to capture the number of contacts with a role on a given date. As I can only have one active relationship, I'm using a Me...
Hi MattTrollope ,
You can create a measure as below to get the number of distinct contacts, please find the details in the attachment.
Number of distinct Contacts = VAR _date = SELECTEDVALUE ( 'Date'[Date] ) RETURN CALCULATE ( DISTINCTCOUNT ( 'Role'[ContactID] ), FILTER ( 'Role', 'Role'[JoinDate] <= _date && 'Role'[CloseDate] >= _date ) )Best Regards
Hi MattTrollope ,
You can create a measure as below to get the number of distinct contacts, please find the details in the attachment.
Number of distinct Contacts =
VAR _date =
SELECTEDVALUE ( 'Date'[Date] )
RETURN
CALCULATE (
DISTINCTCOUNT ( 'Role'[ContactID] ),
FILTER ( 'Role', 'Role'[JoinDate] <= _date && 'Role'[CloseDate] >= _date )
)
Best Regards
Thank you very much for your asistance. This now solves my problem, I wondered if a different approach might be the solution, and you have provided it for me, thank you again.