Distinct values with or statement

[Anonymous]

Hello everybody,

I have got a problem with using distinct values.

Some context:
I'm working with a dashboard where people can select a beginning and ending date with two slicers. I harvest these slicers into a measure (using values) , which i used to create Variables called dynamicbegin and dynamicend (which represents the beginning and end of the period that people choose on the dashboard).

Now I want to count the unique values of a column called  "Combinatie BSN & ZIN" (which has starting and end dates in the table it is from), for where OR the beginning OR the end is inside of the selected period (which is selected by user on dashboard).

I figured out a logic to do this. I first distinctcount all the values in column "Combinatie BSN & ZIN". Then i substract the values from that column of which the starting date is AFTER the ending date of the selected period OR where the ending date is BEFORE
the selected period. This measure is down below.

TESTING = 
VAR dynamicbegin = [Dynamisch begindatum] 
VAR dynamicend = [Dynamisch einddatum]

RETURN

CALCULATE(DISTINCTCOUNT('Data verstrekkingen'[combinatie BSN & ZIN]) 
-
CALCULATE(DISTINCTCOUNT('Data verstrekkingen'[combinatie BSN & ZIN]);
FILTER('Data verstrekkingen';'Data verstrekkingen'[Product begindatum]>dynamicend || 
'Data verstrekkingen'[Product einddatum] < dynamicbegin)))

This logic works perfeclty fine. I already double checked.

Except for the distinct values part. Because I use a or statement (the double pipe symbol "||") there are still duplicates. It unduplicates the part before and after the "||" symbol, but because of that there are still duplicates between those two groups, that are not made unique.

Is there a method to again distinct out all the values from the bottom part of the formula? So of this part.

CALCULATE(DISTINCTCOUNT('Data verstrekkingen'[combinatie BSN & ZIN]);
FILTER('Data verstrekkingen';'Data verstrekkingen'[Product begindatum]>dynamicend || 
'Data verstrekkingen'[Product einddatum] < dynamicbegin))

Or do I (instead of the or "||" statement ) have to split this specific part into two measures and then distinct out those two measures? (If yes how do i do this)


Would be very gratefull if someone has a sollution!

Greatings.

Lucas

[v-frfei-msft]

Hi @Anonymous,

How about using Or instead of ||? 

CALCULATE(DISTINCTCOUNT('Data verstrekkingen'[combinatie BSN & ZIN]);
FILTER('Data verstrekkingen';'OR(Data verstrekkingen'[Product begindatum]>dynamicend, 
'Data verstrekkingen'[Product einddatum] < dynamicbegin))

Regards,

Frank

[Anonymous]

Thanks for your respons! I tried it, but sadly it does not work. It gives the same result as with the "||" symbol. Have you got any other suggestions?

[v-frfei-msft]

Hi @Anonymous,

Could you please share your sample data and excepted result to me, if you don't have confidential data? Please upload your file to One Drive and share the link here.

Regards,

Frank