In this article, I will explain different scenarios about returning a value of a column based on criteria. I have this table and I want to return the early date for each incident (1 condition):

CALCULATE(MIN('table'[argument1]),ALLEXCEPT('table','table'[argument2]))

And also only for level 2 (2 conditions):

CALCULATE(MIN('table'[argument1]),FILTER('table','table'[argument2]="value" && EARLIER('table'[argument3])='table'[argument3]))

NOTE:

• Replace “table”, “argument” and “value” by yours
• For the last date, replace MIN by MAX
• To add a third condition, just add a new argument inside the FILTER

I want to know the support who has the early date for each incident:

CALCULATE(MIN('table'[argument1]),ALLEXCEPT('table','table'[argument2]),'table'[argument3]='table'[argument4])

I have another table called “sheet” like that:

I want to add some columns from “table”, in excel, I have the VLOOKUP function and the equivalent in Power BI is LOOKUPVALUE:

LOOKUPVALUE('table value return'[argument value return],'table value return'[argument value match],'table value compare'[argument value match])

For instance:

Instead of the 'table value compare'[argument value match], I can put a specific criteria. For instance:

LOOKUPVALUE('table value return'[argument value return],'table value return'[argument value match],"criteria")

I can add a second or more criteria by just adding another “argument value match”. For instance:

LOOKUPVALUE('table1'[argument1],'table1'[argument2],'table2'[argument2],'table1'[argument3],'table2'[argument3])

LOOKUPVALUE will not work with multiple values including empty cells, for instance, imagine that the “column 3” of “table” has empty values:

In such situation, I will use this formula:

MAXX(RELATEDTABLE('table'),'table'[argument])

NOTE: LOOKUPVALUE doesn’t need to have a relationship but MAXX needs it