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
neonguyen1803
Frequent Visitor

Asking about Filters on Calculate

Dear all,

 

I have 2 tables below: 

Dim_Channel

neonguyen1803_0-1681404208648.png

Fact sample table Data:

neonguyen1803_2-1681404272930.png

Done for the relationship between 2 tables

I do create a table with column ChannelKey of Dim_Channel as context then create 2 measures below:

  • Quantity (Actual) = SUM('Data'[SalesQuantity]) - SUM('Data'[ReturnQuantity])
  • Quantity actual of Channel 1 = CALCULATE([Quantity (Actual)], Data[channelKey] = 1)
  • Quantity actual of Channel 1 (v2) = CALCULATE([Quantity (Actual)], DIMChannel[ChannelKey] = 1)
I got the below result:
neonguyen1803_4-1681404802719.png

The question is: why does the measure "Quantity actual of Channel 1" doesn't show value in 3 rows (red circled) as measure "Quantity actual of Channel 1 (v2)"

 

Thank you for your time

 

 

1 ACCEPTED SOLUTION
AntrikshSharma
Community Champion
Community Champion

@neonguyen1803 Think about this:

 

When you write DIMChannel[ChannelKey] = 1, this internally becomes

FILTER ( ALL ( DIMChannel[ChannelKey] ), DIMChannel[ChannelKey] = 1 ) that means we make it overwrite any existing filter context on ChannelKey and make CALCULATE use 1 for each row.

 

So when ChannelKey is 2, the Filter Context outside CALCULATE is 2, the DIMChannel[ChannelKey] = 1 OVERWRITES the filter context on the same column so every row is calculated for 1.

 

However, when you use the Fact table and use Data[channelKey] = 1, the filter context in the report is from DIMChannel[ChannelKey], this time there is no OVERWRITE operation instead a new filter is added to the filter context as there are 2 different columns, so at each row both filter context differ.

 

For example at the first row both column have the filter of 1 so it works, but at row 2 the filter on DIMChannel[ChannelKey] is 2 and Data[ChannelKey] = 1, there is an AND operation here, since there are no rows that belong to both 2 AND 1, the engine returns a blank.

 

This is one of the reasons why you should apply filters on Dimensions and not FACT table.

 

To make the calculations same, you could add REMOVEFILTERS ( DIMChannel[ChannelKey] )

CALCULATE(
    [Quantity (Actual)], 
    Data[channelKey] = 1,
    REMOVEFILTERS ( DIMChannel[channelKey] )
)

 

View solution in original post

2 REPLIES 2
FreemanZ
Super User
Super User

hi @neonguyen1803 

Because there is no record when
ChannelKey=1 and ChannelKey=2 or 3 or 4.
It is about execution order of CALCULATE and there are actually two CALCULATE.
AntrikshSharma
Community Champion
Community Champion

@neonguyen1803 Think about this:

 

When you write DIMChannel[ChannelKey] = 1, this internally becomes

FILTER ( ALL ( DIMChannel[ChannelKey] ), DIMChannel[ChannelKey] = 1 ) that means we make it overwrite any existing filter context on ChannelKey and make CALCULATE use 1 for each row.

 

So when ChannelKey is 2, the Filter Context outside CALCULATE is 2, the DIMChannel[ChannelKey] = 1 OVERWRITES the filter context on the same column so every row is calculated for 1.

 

However, when you use the Fact table and use Data[channelKey] = 1, the filter context in the report is from DIMChannel[ChannelKey], this time there is no OVERWRITE operation instead a new filter is added to the filter context as there are 2 different columns, so at each row both filter context differ.

 

For example at the first row both column have the filter of 1 so it works, but at row 2 the filter on DIMChannel[ChannelKey] is 2 and Data[ChannelKey] = 1, there is an AND operation here, since there are no rows that belong to both 2 AND 1, the engine returns a blank.

 

This is one of the reasons why you should apply filters on Dimensions and not FACT table.

 

To make the calculations same, you could add REMOVEFILTERS ( DIMChannel[ChannelKey] )

CALCULATE(
    [Quantity (Actual)], 
    Data[channelKey] = 1,
    REMOVEFILTERS ( DIMChannel[channelKey] )
)

 

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.

Top Solution Authors