Measure that counts unique IDs in a group modified by a filter.

[Anonymous]

I'm trying to create a measure that sums up the number of unique ids (Session IDs) over a group (Account ID) only where date 1 is less than date 2. 

I have managed to only execute for the correct rows but my calculation sums up all rows per Account ID.  

Any suggestions? 

[amitchandak]

@Anonymous , Try a measure like

sumx(values(data[Account ID]), calculate(Distinctcount(data[SessionID])))

[Anonymous]

Thank you @amitchandak however I got the following table following your advice. 

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

You can follow the below steps to get it:

1. Create a calculated column to get the flag using compare date 1 and date 2

Flag = IF('data'[date1]<'data'[date2],1,0)​

2. Create a measure to get the count of sessions

Measure = 
VAR _tab=SUMMARIZE(FILTER(ALLEXCEPT('data','data'[Account Id]),'data'[Flag]=1),"countofSession",CALCULATE(DISTINCTCOUNT('data'[Session ID])))
RETURN IF(MAX('data'[Flag])=1,SUMX(_tab,[countofSession]))

Best Regards