Using LOOKUPVALUE to compare two values with duplicates

Question

New to DAX and its language.&nbsp;I am trying to make a measure that gives feedback if a date from one column is greater than a date in another column. This is for multiple projects that are using the same base template with different dates for each. So duplicate columns are expected.&nbsp;I tried this formula:&nbsp;IsNetworkReadyOnTrackforUAT = IF(LOOKUPVALUE(Tasks[TaskFinishDate],Tasks[ShortTaskName],"NETWORK READY") &gt; LOOKUPVALUE(Tasks[TaskFinishDate],Tasks[ShortTaskName],"UAT"), "IN TROUBLE","ALL GOOD")&nbsp;this produces an error of multiple values. In researching, I tried using calculate with FIRSTNONBLANK like this:IsNetworkReadyOnTrackforUAT = IF(CALCULATE(FIRSTNONBLANK(Tasks[TaskFinishDate], 1),FILTER(ALL(Tasks),Tasks[ShortTaskName] = "UAT"))&gt;CALCULATE(FIRSTNONBLANK(Tasks[TaskFinishDate], 1),FILTER(ALL(Tasks),Tasks[ShortTaskName] = "NETWORK READY")),"IN TROUBLE","ALL GOOD")&nbsp;This returns the same value for all of the rows (each row has a different value).&nbsp; Here is a snapshot of the data.&nbsp;&nbsp;&nbsp;I think I'm missing something basic but unsure.&nbsp;

lbendlin · Answer

your sample data doesn't match your description.&nbsp; It is unclear if you are trying to look up something in the same table or another table (LOOKUPVALUE is usually used for the latter).&nbsp; Please elaborate.

tlh · Answer

No, all the data is contained within the same table.

Here is a summary of what I'm looking to do:

For each PROJECT NAME, find the TASK NAME called "UAT" and select the START date. For each PROJECT NAME, find the TASK NAME called "NETWORK READY" and select the START date. Comp[are these two START dates. If the START date for UAT is greater than the START date for "NETWORK READY", output "GOOD". Else, output "BAD".

I've highlighted two example columns out of my data.

The output here in this case should be GOOD.

TIA

nandic · Answer

tlh&nbsp;, below is formula, i added a condition: if we are missing data for either uat or network days return "no compare".Result&nbsp;=VAR&nbsp;_UatStart&nbsp;=&nbsp;&nbsp;&nbsp;&nbsp;LOOKUPVALUE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Tasks[Start],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Tasks[Task&nbsp;Name],&nbsp;"UAT",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Tasks[Project&nbsp;Name],&nbsp;Tasks[Project&nbsp;Name]&nbsp;&nbsp;&nbsp;&nbsp;)VAR&nbsp;_NetworkStart&nbsp;=&nbsp;&nbsp;&nbsp;&nbsp;LOOKUPVALUE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Tasks[Start],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Tasks[Task&nbsp;Name],&nbsp;"NETWORK&nbsp;READY",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Tasks[Project&nbsp;Name],&nbsp;Tasks[Project&nbsp;Name]&nbsp;&nbsp;&nbsp;&nbsp;)RETURN&nbsp;&nbsp;&nbsp;&nbsp;IF&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Tasks[Task&nbsp;Name]&nbsp;IN&nbsp;{&nbsp;"UAT",&nbsp;"NETWORK&nbsp;READY"&nbsp;},&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;IF&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;AND&nbsp;(&nbsp;NOT&nbsp;(&nbsp;ISBLANK&nbsp;(&nbsp;_NetworkStart&nbsp;)&nbsp;),&nbsp;NOT&nbsp;(&nbsp;ISBLANK&nbsp;(&nbsp;_UatStart&nbsp;)&nbsp;)&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;IF&nbsp;(&nbsp;_NetworkStart&nbsp;&gt;&nbsp;_UatStart,&nbsp;"IN&nbsp;TROUBLE",&nbsp;"ALL&nbsp;GOOD"&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"NO&nbsp;COMPARE"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;

lbendlin · Answer

What is your plan for scenarios when either of the entries is missing or duplicated?

tlh · Answer

Thanks for your help, but still not working. Here is what I'm doing. I duplicated the data into a table called TestTable and updated your formula.&nbsp;No plans for that yet. It should never happen as they are a part of the template used.&nbsp;

Forum Discussion

Using LOOKUPVALUE to compare two values with duplicates

6 Replies

Related content

Duplicate ranking

LOOKUPVALUE with duplicates

lookupvalue

remove duplicate value within a row

lookupvalue PBI