cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rode Regular Visitor
Regular Visitor

Filtering table by variable only

Hi everyone!

 

I have a table called Calendar and it contains some fields as Date, Year, Month, etc.

The second table (the fact one) is related to the previous one by the Year field.

 

I would to do the following computation:

 

Back Margin Variance CAGR = 
CALCULATE(
	[Back Margin];
	FILTER('Calendar'; 'Calendar'[Year] = LASTNONBLANK('Calendar'[Year]; TRUE()))
	)


But it doesn't work as expected because it does no apply the filter on Calendar table. Instead, using the following formula, it performs the expected computation:

 

 

Back Margin Variance CAGR = 
VAR
	LY = LASTNONBLANK('Calendar'[Year]; TRUE())
RETURN
CALCULATE(
	[Back Margin];
	FILTER('Calendar'; 'Calendar'[Year] = LY)
	)

 

Could anyone explain why this happens, please?

Thanks.

4 REPLIES 4
Community Support Team
Community Support Team

Re: Filtering table by variable only

Hi @rode,

 

Would you please share some sample data for further analysis? I need to know the source data in Calendar and Fact table, and the formula of measure [Back Margin] so that I can reproduce your scenario. 

 

Thanks,
Yuliana Gu

 

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
rode Regular Visitor
Regular Visitor

Re: Filtering table by variable only

Hi @v-yulgu-msft,

 

here it is the sample of Calendar:

 

Capture.PNG

 

The formula of back margin is the next one:

Back Margin = 
	CALCULATE(
		SUM(TMM[KPI value]) / 1000;
		TMM[KPI name]="Back Margin Promo" || TMM[KPI name]="Back Margin NoPromo"
	)

where TMM table has some columns, but the interested ones are KPI name (the ones with the names reported above) and KPI value.

 

Thank you so much!

Regards.

austinsense Established Member
Established Member

Re: Filtering table by variable only

Without knowing everything, here's what I think is going on at a very high level.

 

When you use the variable it calculates LY in the right context. With FILTER() it calculates LY in the wrong context.

 

With FILTER() you're calculating LASTNONBLANK for every row of the calendar table, and for each row in the calendar table the LASTNONBLANK is the date in that row.  So for every row in the calendar table this expression is true - Calendar'[Year] = LASTNONBLANK('Calendar'[Year]) - and so you get every row back with no filtering.

Austin is VP Operations at PowerPivotPro and a professional self-service enthusiast Smiley Happy
Highlighted
rode Regular Visitor
Regular Visitor

Re: Filtering table by variable only


@austinsense wrote:

Without knowing everything, here's what I think is going on at a very high level.

 

When you use the variable it calculates LY in the right context. With FILTER() it calculates LY in the wrong context.

 

With FILTER() you're calculating LASTNONBLANK for every row of the calendar table, and for each row in the calendar table the LASTNONBLANK is the date in that row.  So for every row in the calendar table this expression is true - Calendar'[Year] = LASTNONBLANK('Calendar'[Year]) - and so you get every row back with no filtering.


 

Hi @austinsense and thank you for your reply.

I was thinking the same thing. But I'm so curious to know which is the detail which I'm not getting in touch with to explain the reason of this strange behaviour.

Thank you.