topic Re: ALL() and REMOVEFILTER() Don't Work Correctly on My Report in DAX Commands and Tips

ALL() and REMOVEFILTER() Don't Work Correctly on My Report

vkisa — Sat, 30 Mar 2024 18:36:06 GMT

Hi,

I have a problem that showing Lifetime amount by using ALL() or REMOVEFILTERS() functions on my report.

I have created a new date table as slicer that implies Yesterday, Last 7 Days, Same Day Last Month, All, Custom etc., and it works perfect. But when I selected any slicer rather than "All", Lifetime Revenue does not show correct amount.

Since the new slicer table consists duplicate dates, the cross-filter direction on relationship is not single as below.

The dax query, I've used is to get Life Time Revenue:

Revenue_Lifetime = CALCULATE( SUM(Revenue[Revenue]), ALL('Date Periods'[Date],'Date Periods'[Type]) )

Here is a sample report that shows the problem,

https://we.tl/t-Q0SBtDHTFn

Thank you,

Kisa

Re: ALL() and REMOVEFILTER() Don't Work Correctly on My Report

tamerj1 — Sat, 30 Mar 2024 21:42:41 GMT

Hi vkisa

this could be due to a sort by column (I would guess the [Order] column. You can include this column inside ALL or just use ALL ( 'Date Periods' )

Re: ALL() and REMOVEFILTER() Don't Work Correctly on My Report

vkisa — Sat, 30 Mar 2024 22:33:51 GMT

Thank you tamerj1 ,I tried your suggestion but it still does not affect well. When I selected 'Yesterday', Lifetime Revenue is not same with 'Yesterday' but not equal to Lifetime total as well.

Re: ALL() and REMOVEFILTER() Don't Work Correctly on My Report

lbendlin — Sun, 31 Mar 2024 00:20:15 GMT

That's because you have a Top 10 filter on the right table. tamerj1 provided the right solution - ALL('Date Periods')

Re: ALL() and REMOVEFILTER() Don't Work Correctly on My Report

vkisa — Sun, 31 Mar 2024 01:24:37 GMT

I don't know why Top10 affects the output. But it didn't solve this. Are you able to download and look at the report ?

Thanks for reply.

Re: ALL() and REMOVEFILTER() Don't Work Correctly on My Report

lbendlin — Sun, 31 Mar 2024 13:51:31 GMT

I did, and it works as expected.

Re: ALL() and REMOVEFILTER() Don't Work Correctly on My Report

vkisa — Sun, 31 Mar 2024 15:18:48 GMT

Thank you lbendlin and tamerj1 , it worked after I removed Top10 selection. I create a measure for getting top 10 by using rankx and it did not affect lifetime revenue amounts. I guess there is a different between dafault TopN selection on filters and RANKX function.