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...
lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
6 years ago

your sample data doesn't match your description.  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).  Please elaborate.

  • tlh's avatar
    tlh
    Frequent Visitor
    6 years ago

    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'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?