Forum Discussion

eiprakash's avatar
eiprakash
Frequent Visitor
8 years ago
Solved

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 ...
Need Help
  • dedelman_clng's avatar
    dedelman_clng
    8 years ago

    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

  • dedelman_clng's avatar
    dedelman_clng
    8 years ago

    Remove any existing filters on Emp by using ALL

     

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

     

dedelman_clng's avatar
dedelman_clng
Icon for Community Champion rankCommunity Champion
8 years ago

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's avatar
eiprakash
Frequent Visitor
8 years ago

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.