Forum Discussion

Lumegu's avatar
Lumegu
Frequent Visitor
4 years ago
Solved

Obtain date from different table

Dear community,

 

I have the following issue for which I hope one of you could perhaps help me out:

 

There are two data sets:

1. Contains items:

ItemStatus itemDue date activity B (to be calculated)
Item 1In progress1-12-2021
Item 2In progress13-12-2022

 

2. Contains activities to these items

ItemActivityStatus activityDue date activity
Item 1A In progress1-1-2022
Item 1BIn progress1-12-2021
Item 1CIn progress1-10-2021
Item 1DIn progress2-12-2021
Item 1EDone3-12-2021
Item 2AIn progress1-1-2022
Item 2BDone13-12-2022
Item 2CDone1-10-2021

 

I would like to obtain the due date for activity B (dataset activities) for each item in the items dataset (Due date activity B (to be calculated)). I was wondering how to formulate this (vlookup type of calc) into a DAX formula?

  • smpa01's avatar
    smpa01
    4 years ago

    Lumegu  try this measure

    Measure = CALCULATE(MAX('Table 2'[Due date activity]),FILTER('Table 2','Table 2'[Activity]="B"))

     

     

2 Replies

  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Lumegu  try this measure

    Measure = CALCULATE(MAX('Table 2'[Due date activity]),FILTER('Table 2','Table 2'[Activity]="B"))

     

     

  • VahidDM's avatar
    VahidDM
    Icon for Super User rankSuper User
    4 years ago

    Hi Lumegu 

     

    If you want to add a column to your table 1, try this code to add a calculated column:

     

    Due date activity B =
CALCULATE (
    MAX ( 'Table 2'[Due date activity] ),
    FILTER (
        'Table 2',
        'Table 2'[Activity] = "B"
            && 'Table 2'[Item] = EARLIER ( 'Table'[Item] )
    )
)

     

    output:

     

     

     

    if you want a measure, use smpa01  solution.

     

    If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
    Appreciate your Kudos!!
    LinkedIn:     www.linkedin.com/in/vahid-dm/