Filtering ALL + other dimensions

[Anonymous]

Hello Community,

Here I am again with an issue on my reports.

What I want to do (and I did manage to do it) is to display count on YEAR and YEAR-1, which is quite simple.

To avoid context issues, I used FILTER(ALL()) for the YEAR-1 calculation, in order to compare measures side by side.

I also had to add another filter, as my fact data need to be restricted.

Here is my formula (it's a template, but everything is there) :

Count Year Y-1 = CALCULATE(DISTINCTCOUNT('FactTable'[Number]),FILTER(ALL('FactTable'),
OR('FactTable'[date] = BLANK(),'FactTable'[dateCancel > RELATED('dateTable'[dateEnd])))
,FILTER(ALL('dateTable'),'dateTable'[date] = MAX('dateTable'[date]) - 1))

And this, is giving me the result I want, working lovely. But...

As I used a FILTER(ALL()) there :

FILTER(ALL('FactTable')

The measure is not taking account of any other dimension filter (example : I have an Organisation dimension which is related to the FACT TABLE, and when I apply filter on one organisation, my Y measure is changing as I expect, but my Y-1 measure is just showing the result of the whole data).

Thing is I've tried with ALLSELECT and even without anything (only FILTER()) but the result I get is blank.

Any idea about which expression could do the trick here ?

Thank you for reading me 🙂

Regards,

Heykel

[Anonymous · Accepted solution]

So I did find a solution to make it work.

I simply used variables to retrieve the [dateEnd] from my dimension into this variable, and I used CALCULATE filters directly, by doing :

'FactTable'[date] = BLANK() || 'FactTable'[date] > dateEnd

I hope this could help some people.

Regards,

Heykel

[Anonymous]

Just for information, I manage to make this work :

CALCULATE(DISTINCTCOUNT('FactTable'[Number]),FILTER(ALL('dateTable'),'dateTable'[date] = MAX('dateTable'[date]) - 1) , 'FactTable'[date] = BLANK())

But doing that, I'm losing the condition 

'FactTable'[dateCancel > RELATED('dateTable'[dateEnd])

Is there any way to use RELATED() without FILTER() ? Or maybe the only way is to create a column to retrieve the information from the dimension table to the fact table ?