Forum Discussion

tlh's avatar
tlh
Frequent Visitor
6 years ago

Using LOOKUPVALUE to compare two values with duplicates

New to DAX and its language.  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 s...
nandic's avatar
nandic
Icon for Resident Rockstar rankResident Rockstar
6 years ago

tlh , below is formula, i added a condition: if we are missing data for either uat or network days return "no compare".

Result =
VAR _UatStart =
    LOOKUPVALUE (
        Tasks[Start],
        Tasks[Task Name], "UAT",
        Tasks[Project Name], Tasks[Project Name]
    )
VAR _NetworkStart =
    LOOKUPVALUE (
        Tasks[Start],
        Tasks[Task Name], "NETWORK READY",
        Tasks[Project Name], Tasks[Project Name]
    )
RETURN
    IF (
        Tasks[Task Name] IN { "UAT", "NETWORK READY" },
        IF (
            AND ( NOT ( ISBLANK ( _NetworkStart ) ), NOT ( ISBLANK ( _UatStart ) ) ),
            IF ( _NetworkStart > _UatStart, "IN TROUBLE", "ALL GOOD" ),
            "NO COMPARE"
        )
    )


 

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
6 years ago

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

  • tlh's avatar
    tlh
    Frequent Visitor
    6 years ago

    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.

     

    No plans for that yet. It should never happen as they are a part of the template used. 

    • nandic's avatar
      nandic
      Icon for Resident Rockstar rankResident Rockstar
      6 years ago

      tlh there are fields underlined with red line, it means that there is no such field in your table/model. Check these and make sure you type it correctly.

      Another thing, if there are no underlined fields and you get error that it cant get single value (as on screenshot) it means that there is still column in table which we need to add in lookup as on current lookup parameters dax cant find unique value.