Then I created few measures adding "ALL('Date.1'[Date]),USERELATIONSHIP('Date.1'[Date],'Date.2'[Date])" within my Calculate function. Most of the time it works perfectly but I have a problem whenever I need to use an other Filter function (see example below) in my measure. It looks like if my selected range for "Date.2" is not within Date.1 result is wrong. Either the starting date or ending date of Period-1 will still affect my measure for Period-2 which is obviously not what I want.
Here is an example:
- This formula counts the number of different production line being operated (meaning with production >0) over a specific period:
DIFF LINE PREV. = CALCULATE(DISTINCTCOUNT(Database[LINE #]),FILTER(Database,[DAILY PLAN Prev. P.]>0),ALL('Date.1'[Date]),USERELATIONSHIP('Date.1'[Date],'Date.2'[Date]))
- The underlying formula for production (Daily Plan Prev. P.) is:
DAILY PLAN Prev. P. = CALCULATE(SUM(Database[DAILY PLAN]),ALL('Date.1'),USERELATIONSHIP('Date.1'[Date],'Date.2'[Date]))
If Date.2 is withing Date.1 it works fine but now if Date.1 is before Date.2 result is "(Blank)". I guess I am missing something in
"Filter(Database[Daily Plan Prev. P]>0)" as if I delete this part of the formula Date.1 no longer affects my measure. However result is still wrong as it returns all lines even if the production is 0.
Does anyone know what I am missing here to completely segregate this measure from my Date.1 filter?
I actually found a workaround by editing the interaction of the first Date filter so that it does not affect this KPI but it is not ideal. I would rather fix the formula itself. Will share shortly data + output.