Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Dear all,
I have 2 tables below:
Dim_Channel
Fact sample table Data:
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:
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
Solved! Go to Solution.
@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] )
)
@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] )
)