Using ALL() on Date column to ignore Date Slicer

[danthu]

Hello,

I have a table (Table) with two different columns containing dates (Column1 and Column2). I have a report with two different date slicers (Slicer1 and Slicer2), one for each of the date columns. Then I have two measures, Measure1 that is filtered by Slicer1 and Measure2 that is filtered by Slicer2. So far so good.

Then I would like to create another measure as Measure3 = (Measure2 / Measure1). I want both slicers to affect this measure, but Slicer1 should only affect Measure1 and Slicer2 should only affect Measure2. I have tried to solve by modifying the measures like:

Measure1 = CALCULATE(COUNTA(Table[Column1]), ALL(Table[Column2]))

Measure2 = CALCULATE(COUNTA(Table[Column2]), ALL(Table[Column1]))

I thought this would cause Measure1 to ignore any filters or slicers applied to Column2 and vice versa. But it has no effect, Measure1 is still affected by both Slicer1 and Slicer2. However if I write

Measure1 = CALCULATE(COUNTA(Table[Column1]), ALL(Table)

I get the expected result, now Measure1 is not affected by either Slicer1 or Slicer2. 

Can someone please explain to me what I am missing and help me to solve this?

Thank you!

[danthu]

Hi,

I still have not managed to solve this issue. Anybody got an idea of what to try?

Thanks

[dilumd]

https://www.youtube.com/watch?v=q4jePhzKtbU

Valuable videos on ALL Function.

[danthu]

Thanks,

I still don't understand why it does not work in my case. Could you please explain why I'm getting that behaviour?

Simply what I want to do is divide the outcome of Measure2 with the outcome of Measure1 when Measure1 is only filtered by Slicer1 and Measure2 only filtered by Slicer2. Is there any way to work around this and store the outcomes of my separate measures and then just dividing them?

It seems so simple, I just want to divide two numbers that are there in my report, still I can't figure out how to do it.

Any suggestions are very much appreciated!

[v-qiuyu-msft]

Hi @danthu,

Please use ALLEXCEPT() function in your measures:

Measure1 = CALCULATE(COUNTA('Table'[Column1]), ALLEXCEPT('Table','Table'[Column1]))

Measure2 = CALCULATE(COUNTA('Table'[Column2]), ALLEXCEPT('Table','Table'[Column2]))

Best Regards,
Qiuyun Yu

[danthu]

@v-qiuyu-msft

Thanks for your response!

Unfortunately I have allready tried your suggestion and I get the same result. I cannot understand why it happens. Any ideas?