Forum Discussion

laurent_rio's avatar
laurent_rio
Helper I
5 years ago
Solved

How to do LOOKUP Correctly ?

Hi, 

 

I have two set tables like this 

 

One table is employee list that generated every end quarter, for this mean end of Q4 2020 and other is trained employee list 

Now i want to calculate number of employee that has been trained per quarter 

For example for quarter 4 2020 --> mean to get total employee that has been trained, i need to look up from trained employee table employee that been trained < 1 Jan 2021 

in this case = B,F,G,J,O

 

Can enlight how to create DAX to connect/vlookup this two table ? 

Need Help
Show and Tell
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    Hi, laurent_rio 

    Thanks for the feedback.

    In that case, you need a separate date table for the slicer.

    And the measure has to be written differently.

     

    Check the below picture and the sample pbix file's link down below.

    All measures are in the sample pbix file.

     

     

    2 Trained Employees count =
    VAR todayslicermonth =
    CALCULATE (
    SELECTEDVALUE ( SlicerDates[MonthOffset] ),
    FILTER ( ALLSELECTED ( SlicerDates ), SlicerDates[Date] = TODAY () )
    )
    VAR slicermonth =
    SELECTEDVALUE ( SlicerDates[MonthOffset], todayslicermonth )
    VAR slicermonthsixmonthbefore = slicermonth - 5
    VAR result =
    CALCULATE (
    COUNTROWS ( Training ),
    FILTER ( ALL ( DimDates ), DimDates[Date] <= MAX ( DimDates[Date] ) )
    )
    RETURN
    IF (
    SELECTEDVALUE ( DimDates[MonthOffset] ) >= slicermonthsixmonthbefore
    && SELECTEDVALUE ( DimDates[MonthOffset] ) <= slicermonth,
    result,
    BLANK ()
    )
     
     

    Hi, My name is Jihwan Kim.


    If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


    Linkedin: https://www.linkedin.com/in/jihwankim1975/

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    5 years ago

    Hi, laurent_rio 

    Please check the below picture and the sample pbix file's link down below, whether it is what you are looking for.

    All measures are in the sample pbix file, and all steps are numbered in front of each measure.

     

     

    https://www.dropbox.com/s/vqw7eg7s9pkd115/laurentrio.pbix?dl=0 

     

     

    Hi, My name is Jihwan Kim.

     

    If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

     

    Linkedin: https://www.linkedin.com/in/jihwankim1975/

  • laurent_rio's avatar
    laurent_rio
    Helper I
    5 years ago

    Thanks Jihwan_Kim 
    it helps

    Now , how for example i want to show in the chart, the number of people train in the last 6 months ?

    So i will have month-year slicer (for example Mar-2021)

    I want to show in my chart 
    X axis = last 6 month from now ( November-April 2021) vs Y axis the number of people has been trained during that month ( which will be cummulative)


    Thanks before

    • Jihwan_Kim's avatar
      Jihwan_Kim
      Super User
      5 years ago

      Hi, laurent_rio 

      Thanks for the feedback.

      In that case, you need a separate date table for the slicer.

      And the measure has to be written differently.

       

      Check the below picture and the sample pbix file's link down below.

      All measures are in the sample pbix file.

       

       

      2 Trained Employees count =
      VAR todayslicermonth =
      CALCULATE (
      SELECTEDVALUE ( SlicerDates[MonthOffset] ),
      FILTER ( ALLSELECTED ( SlicerDates ), SlicerDates[Date] = TODAY () )
      )
      VAR slicermonth =
      SELECTEDVALUE ( SlicerDates[MonthOffset], todayslicermonth )
      VAR slicermonthsixmonthbefore = slicermonth - 5
      VAR result =
      CALCULATE (
      COUNTROWS ( Training ),
      FILTER ( ALL ( DimDates ), DimDates[Date] <= MAX ( DimDates[Date] ) )
      )
      RETURN
      IF (
      SELECTEDVALUE ( DimDates[MonthOffset] ) >= slicermonthsixmonthbefore
      && SELECTEDVALUE ( DimDates[MonthOffset] ) <= slicermonth,
      result,
      BLANK ()
      )
       
       

      Hi, My name is Jihwan Kim.


      If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


      Linkedin: https://www.linkedin.com/in/jihwankim1975/