Dax lookup and return value basesd on 2 possible matches.

[Flammie]

Not sure if this is possible. I have 2 tables. One has a ton of fields so I didn't copy it but the 2 fields in the big table that I'm working with is "Item Number" and "Item Descritption". The smaller table is pictured below. The two tables are not related in my relationships. I need to have a column in the big table named "Entity" where I lookup the item number or the item description (if the item number is missing) and return what Entity is associated. If both fields are empty then return "NONE").  My current code is below and it works sometimes which doesn't make sense because the code isn't correct, I know.  I also can't get it to look at one field if the other is blank which is why that part of the code has been deleted.

Entity = LOOKUPVALUE(ItemData[Entities],ItemData[Item Number],Page1_1[Item Number],"None")

Here is what I want it to say in DAX - Entity = if itemNumber is not null then use item number to retrieve the entity name, otherwise use the itemdescription to find the entity.

Here is what I would like to see:

Item number = "123"

Item Description = "Sunshine"

Entity = "Florida"

I can pull item number and description from the big table. I just need to match those with the small table to get the entity.

[Flammie · Accepted solution]

I took the blanks out of my tables and just put the solution into the formula. So now my table has no duplicates and I can make the correct relationship. For my formula, I just put that if there are blanks, just say "Other".

[alexvc]

Hi there,

You can try this formula. Basically first is evaluating if Item Number is blank or null, if its not it goes straight to lookup the value using it. If it is then it evaluates if Item description is blank or null, if it is then it returns none. If not then looks up entity using itemdescription.

Let me know if it works

Entity = IF(OR(ISBLANK(ItemData[Item Number])=TRUE,ItemData[Item Number] = "NULL"),IF(OR(ISBLANK(ItemData[Item Description])=TRUE,ItemData[Item Description] = "NULL"),"None",LOOKUPVALUE(ItemData[Entities],ItemData[Item Description],Page1_1[Item Description])),LOOKUPVALUE(ItemData[Entities],ItemData[Item Number],Page1_1[Item Number]))

[Flammie]

---

@alexvc wrote:

---

Hello. No it did not work. I am getting the same error as when I tried it before. I think the lookup wants to only look up a specific value like "Dog". At least that's how it seems because if I add another search value in, the formula works. Here is the error. and when I added another lookup value.

[Anonymous]

@Flammie - That error occurs because the lookup finds multiple rows with different return values. You need to have an alternate value for that case.

[Flammie]

---

@Anonymous wrote:

@Flammie - That error occurs because the lookup finds multiple rows with different return values. You need to have an alternate value for that case.

---

I'm getting this error. I am pretty sure it is because I can't make a 1 to many relationship between the two tables but I don't know how to fix it. I even tried to make a "related" table but that doesn't work either. Neither table has unique columns so I'm sure that has something to do with it.

---

---

Won't let me do a 1 to many relationshipError in code

[Anonymous]

@Flammie - You can't have duplicate values on the "1" side of the relationship.

Getting back to the original question - it seems that the data doesn't work with a LOOKUPVALUE, because different results are returned for a single input value.

What is the requirement in this case? Would you want it to say "Multiple Values" or some such message, or find the first related value? Or something else? 

Cheers,

Nathan

[Flammie]

---

@Anonymous wrote:

@Flammie - You can't have duplicate values on the "1" side of the relationship.

Getting back to the original question - it seems that the data doesn't work with a LOOKUPVALUE, because different results are returned for a single input value.

What is the requirement in this case? Would you want it to say "Multiple Values" or some such message, or find the first related value? Or something else? 

Cheers,

Nathan

---

The item numbers in the "ItemData" table only happen once. It's the [Item Description] that is the same for many products. So in the case of the description, I would say "Just use the first one you come across". But that doesn't explain why it won't even allow the "ItemData" table to be used in that expression.

[Anonymous]

@Flammie  - Try the following. I added the part in bold. It won't lookup the value, but I think it should fix the error.

Entity =
IF (
    OR ( ISBLANK ( ItemData[Item Number] ) = TRUE, ItemData[Item Number] = "NULL" ),
    IF (
        OR (
            ISBLANK ( ItemData[Item Description] ) = TRUE,
            ItemData[Item Description] = "NULL"
        ),
        "None",
        LOOKUPVALUE (
            ItemData[Entities],
            ItemData[Item Description], Page1_1[Item Description],
            "Multiple"
        )
    ),
    LOOKUPVALUE ( ItemData[Entities], ItemData[Item Number], Page1_1[Item Number] )
)

[Flammie]

I may have figured it out. I didn't know that blank fields were counted as duplicate entries. I made the relationship many to many. It seems to have worked. I am doing some validation and then I'll update everyone. Thank you!

[Anonymous]

Great!

[Flammie]

---

@Anonymous wrote:

@Flammie  - Try the following. I added the part in bold. It won't lookup the value, but I think it should fix the error.

Entity =
IF (
    OR ( ISBLANK ( ItemData[Item Number] ) = TRUE, ItemData[Item Number] = "NULL" ),
    IF (
        OR (
            ISBLANK ( ItemData[Item Description] ) = TRUE,
            ItemData[Item Description] = "NULL"
        ),
        "None",
        LOOKUPVALUE (
            ItemData[Entities],
            ItemData[Item Description], Page1_1[Item Description],
            "Multiple"
        )
    ),
    LOOKUPVALUE ( ItemData[Entities], ItemData[Item Number], Page1_1[Item Number] )
)

---

I think that both of these codes would work if I could get DAX to see the ItemData table. It doesn't even give it to me as an option.