Creating a measure that passes measure from different table

[eiprakash]

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")

[dedelman_clng · Accepted solution]

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

[v-shex-msft]

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

[eiprakash]

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 · Accepted solution]

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

[eiprakash]

This is great ! It worked thanks.

As a followup from the same expression. I am trying to solve the following.

When user selects 2018, I would like to calculate a measure that does the following:

CALCULATE ( COUNT ( Emp[Pernr] ),
Filter(Emp, Emp[Date] = MIN('Date'[Date])-1
))

MIN('Date'[Date])-1 = 12/31/2017

Since I mention an explicit filter it filters Emp table to 2018 so this above measure returns a blank.

Greatly appreciate your help.

[dedelman_clng · Accepted solution]

Remove any existing filters on Emp by using ALL

CALCULATE ( COUNT ( Emp[Pernr] ),
Filter(ALL(Emp), Emp[Date] = MIN('Date'[Date])-1
))

[eiprakash]

When I use ALL(Emp) function it is now not interacting with other filters. I would like to be able to do something like 

- Filter Employees by other filters except date

[dedelman_clng]

Try setting the date value as a variable, then use ALLSELECTED

Measure = var Dt = calculate(MIN('Date'[Date])-1)
return
 CALCULATE ( COUNT ( Emp[Pernr] ),
   Filter(ALLSELECTED(Emp), Emp[Date] = Dt)
)

If that's still not working you may want to revisit why Date and Emp have an active relationship.  Not all dates in the model necessarily need to be tied back to a calendar table.

Hope this helps

David

[eiprakash]

Thanks much ! Perfect worked well!