CALCULATE strange behaviour

[Mer333]

I have a two linked tables and want to calculate related value from one table by last date from another table but CALCULATE function always do it somehow wrong...

I'm trying to use this formula:

CALCULATE(VALUES('First table'[column2]),FILTER(ALL('First table'),LASTDATE('Second table'[column1])))


It doesn't work at all and when I'm trying to debug it by changing it to calculate countrows it always show the wrong number (37 instead of just 1):


CALCULATE(COUNTROWS(VALUES('First table'[column2])),FILTER(ALL('First table'),LASTDATE('Second table'[column1])))


Any ideas? What am I doing wrong?

 

[Mer333]

CALCULATE(COUNTROWS(VALUES('First table'[column2])),FILTER(ALL('First table'),'First table'[column1]=LASTDATE('Second table'[column1]))) provides the same result

 

[Mer333]

Also I found an interesting dependence:


If I have an equal tables and change relationship direction or use LASTDATE from LOOKUP table to find a value in FACT table then formula below works:


CALCULATE(VALUES('First table'[column2]),LASTDATE('Second table'[column1]))


But I need to find a value in LOOKUP table

 

[Mer333]

FINALLY!!!

It took 5 hours....

CALCULATE(COUNTROWS(VALUES('First table'[column2])),FILTER(ALL('First table'),'First table'[column1]=MAX('Second table'[column1])))

http://sqlblog.com/blogs/marco_russ...filter-it-depends-dax-powerpivot-tabular.aspx

This article saved me...