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

     

eiprakash's avatar
eiprakash
Frequent Visitor
8 years ago

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's avatar
dedelman_clng
Icon for Community Champion rankCommunity Champion
8 years ago

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