Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
matthewhaggett
Regular Visitor

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 exampleData exampleResult in second table if March 2019 is selectedResult in second table if March 2019 is selected

1 ACCEPTED SOLUTION
Anonymous
Not applicable

try this measure:

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

Calculate Table.png

View solution in original post

2 REPLIES 2
Ashish_Mathur
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

try this measure:

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

Calculate Table.png

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.