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

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

    Thanks much ! Perfect worked well!

  • 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