Forum Discussion
Creating a measure that passes measure from different table
I have the below tables, Emp and Date. Connected by Date.
In the front end I have user selecting the Year from Date Table. I am trying to create a measure, that calculates count of Pernr from my Emp table in the following condition. So if user selects current year (2018), the measure should be something like the below
calculate(count(Emp[Pernr]),Emp[Date]=First date of current year from Date table -1)
First date of current year from Date table -1 = 1/12018 - 1 = 12/31/2017
which will then give me something like the below:
calculate(count(Emp[Pernr]),Emp[Date]="12/31/2017")
To get around that error you have to do an explicit filter
formula = CALCULATE ( COUNT ( Emp[Pernr] ), FILTER(Emp, Emp[Date] = MAX ( Emp[Date] ) - 1 ) )
Hope this helps
David
Remove any existing filters on Emp by using ALL
CALCULATE ( COUNT ( Emp[Pernr] ),
Filter(ALL(Emp), Emp[Date] = MIN('Date'[Date])-1
))
8 Replies
Hi eiprakash,
You can try to use below formula if it works for your scenario:
formula = CALCULATE ( COUNT ( Emp[Pernr] ), Emp[Date] = MAX ( Emp[Date] ) - 1 )
Regards,
Xiaoxin Sheng
I was not able to use the measure. I get the below error
A function 'MAX' has been used in a True/False expression that is used as a table filter expression. This is not allowed.
- dedelman_clng
Community Champion
To get around that error you have to do an explicit filter
formula = CALCULATE ( COUNT ( Emp[Pernr] ), FILTER(Emp, Emp[Date] = MAX ( Emp[Date] ) - 1 ) )
Hope this helps
David
