DAX LOOKUPVALUE, RELATED - writing a measure

[asparagus1_]

Hi,

I have three tables, and I want to identify which vendors from the Products table are also present in the Order Ext. Details table. The goal is to create a measure that returns "Yes" when we can find a non-blank Shop value from the Order Ext. Details table. This will help us determine which vendors are assigned to shops and which are not.

I hope this explanation is clear. I attempted to use LOOKUPVALUE/RELATED, but I didn't get the expected results. Here's what I tried:

LOOKUPVALUE('Products'[Vendor], Products[Product_ID], LOOKUPVALUE('Bridge'[Product_ID], 'Bridge'[Market_ID], 'Orders Ext. Details'[Market_ID]))

[uzuntasgokberk · Accepted solution]

Hello @asparagus1_ ,

There is a 1to*(single) relationships your data model. You can use two methos that are change relationship from single to both or use crossfilters function in your dax(it is best practice mostly). İf you choose first way you'll notice when you put vendor column(Products) to table and Shop column(Order Ext. Details). You'll see blank and non-blank shops and then you can filter to show non-blank values from filter pane. I hope it helps.

Best Regards,
Gökberk Uzuntaş

LinkedIn: https://www.linkedin.com/in/g%C3%B6kberk-uzunta%C5%9F-b43906198/

Medium: https://medium.com/@uzuntasgokberk

[v-jiewu-msft · Accepted solution]

Hi @asparagus1_ ,

Based on the information, try using the following DAX formula.

Vendor Has Shop =
VAR CurrentProduct = SELECTEDVALUE(Products[Product_ID])
VAR RelatedMarkets =
    CALCULATETABLE(
        VALUES(Bridge[Market_ID]),
        Bridge[Product_ID] = CurrentProduct
    )
VAR ShopFound =
    CALCULATE(
        SELECTEDVALUE('Orders Ext. Details'[Shop]),
        FILTER(
            'Orders Ext. Details',
            'Orders Ext. Details'[Market_ID] IN RelatedMarkets &&
            NOT(ISBLANK('Orders Ext. Details'[Shop]))
        )
    )
RETURN
    IF( NOT ISBLANK(ShopFound), "Yes", "No" )

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Share some data to work with, explain the question and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[uzuntasgokberk · Accepted solution]

Hello @asparagus1_ ,

There is a 1to*(single) relationships your data model. You can use two methos that are change relationship from single to both or use crossfilters function in your dax(it is best practice mostly). İf you choose first way you'll notice when you put vendor column(Products) to table and Shop column(Order Ext. Details). You'll see blank and non-blank shops and then you can filter to show non-blank values from filter pane. I hope it helps.

Best Regards,
Gökberk Uzuntaş

LinkedIn: https://www.linkedin.com/in/g%C3%B6kberk-uzunta%C5%9F-b43906198/

Medium: https://medium.com/@uzuntasgokberk

[asparagus1_]

Thank you! This is what I was looking for.