Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Sasha
Helper III
Helper III

What happens when table used as a whole in calculate?

Hi,

this is rather a general question to make sure I don't do logical mistakes.

What happens when you use a table as a filter in caclulate without putting filter on it or ALL etc?

Consider the following example.

I want to caclulate total sales in the last three months of the data.

To make sure it's always the same months I want I made a connection between data table and my sales table on M_Diff.

M_Diff in dates table is thedifference in months from fact table's max date. 

Next, I set a filter on the page of Dates[M_Diff] is 0,1,2 - meaning the last quarter.

I then drill through to other page where I compare between the following measures:

Total_Sales1 = 

CALCULATE(SUM(Sales[Sales]), FILTER(ALL(Sales[M_diff]), Sales[M_diff]=0 || Sales[M_diff]=1 || Sales[M_diff]=2))
 

Total_Sales2 = 

CALCULATE(SUM(Sales[Sales]), Dates[M_diff], FILTER(ALL(Sales[M_diff]), Sales[M_diff]=0 || Sales[M_diff]=1 || Sales[M_diff]=2))
 

Total_Sales3 = 

CALCULATE(SUM(Sales[Sales]), ALL(Dates[M_diff]), FILTER(ALL(Sales[M_diff]), Sales[M_diff]=0 || Sales[M_diff]=1 || Sales[M_diff]=2))

 

1 and 3 give the desired answer while 2 doesn't and seem to ignore all the sales in M_Diff=0 (no other filter implied).

I know the addition in 3 about ALL(Dates[M_Diff]) is not necessary, but why does 2 ignore M_Diff=0?

 

I did the same exercise while remaining with the filter page on 0,1,2, but changing the measures to 3,4,5(previous quarter) and all three of them showed the same result - so I'm quite clueless why.

 

I know time intelligence can be used, but this example is important for me to understand further in my work.

 

Thanks!

1 ACCEPTED SOLUTION
AlB
Super User
Super User

Hi @Sasha 

It all comes from the Dates[M_diff] argument. What are you trying to do exactly with that? Note that you are not using a typical condition such as Dates[M_diff] = 0 but just Dates[M_diff]. Effectively, that argument will filter the table with the condition Dates[M_diff], i.e., where Dates[M_diff] is TRUE(), i.e., where Dates[M_diff] <> 0. That is why you are losing the zeros; you are filtering them out. In the case of the previous quarter (3,4,5) you are not looking at zeros and although they are being filtered out as well, you do not notice the difference.         

Total_Sales2 =
CALCULATE (
    SUM ( Sales[Sales] ),
    Dates[M_diff],
    FILTER (
        ALL ( Sales[M_diff] ),
        Sales[M_diff] = 0 || Sales[M_diff] = 1 || Sales[M_diff] = 2
    )
)

 

Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.

Cheers  Datanaut

View solution in original post

2 REPLIES 2
AlB
Super User
Super User

Hi @Sasha 

It all comes from the Dates[M_diff] argument. What are you trying to do exactly with that? Note that you are not using a typical condition such as Dates[M_diff] = 0 but just Dates[M_diff]. Effectively, that argument will filter the table with the condition Dates[M_diff], i.e., where Dates[M_diff] is TRUE(), i.e., where Dates[M_diff] <> 0. That is why you are losing the zeros; you are filtering them out. In the case of the previous quarter (3,4,5) you are not looking at zeros and although they are being filtered out as well, you do not notice the difference.         

Total_Sales2 =
CALCULATE (
    SUM ( Sales[Sales] ),
    Dates[M_diff],
    FILTER (
        ALL ( Sales[M_diff] ),
        Sales[M_diff] = 0 || Sales[M_diff] = 1 || Sales[M_diff] = 2
    )
)

 

Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.

Cheers  Datanaut

@AlB  Thanks!

Actually, I made this formula by mistake and then its result bothered me because I felt I don't understand something crucial and indeed it was the case 🙂

Thank you!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.