Forum Discussion

matthewhaggett's avatar
matthewhaggett
Regular Visitor
7 years ago
Solved

Lookup based on 2 criteria

Hello

 

I have a problem that I am trying to solve and cannot get it sorted currently

 

I have a table of data from Azure Devops which includes history for every item in the system.  This data has multiple lines with the same Work ID each of which has a Date field which states when the history was taken. 

 

I have created a new table that has just the unique work IDs and I want to bring through one of the other fields in the main table based on the workID and also the Date field (So a lookup with 2 criteria) but the Date field will be controlled by a slicer and I cannot work out how to create this

 

Data exampleResult in second table if March 2019 is selected

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    try this measure:

    Measure = 
IF ( HASONEVALUE(Table1[Info]),
CALCULATE(
    VALUES( Table1[Info]),
    CALCULATETABLE(
        Table1,
        ALLSELECTED(Table1[Date])
    )
)
)

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    try this measure:

    Measure = 
IF ( HASONEVALUE(Table1[Info]),
CALCULATE(
    VALUES( Table1[Info]),
    CALCULATETABLE(
        Table1,
        ALLSELECTED(Table1[Date])
    )
)
)

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    Hi,

    Select any date in the slicer.  Drag the Work Item ID and Info field to the Table visual.