Combine distinct with lookup

[diablo9082]

I have two tables in my model, Payment and VendorMast. Each has a column named Vendor, with VendorMast having all Vendors in the sytem. I'm trying to create a table that has the distinct values of Vendor, but only those found in Payment table. 

I can do DISTINCT(Payment[Vendor]) and get the distinct Vendor values in Payment, but I also want to lookup a Vendor_Name in VendorMast with those values. How can I accomplish this? 

Payment

- Vendor

VendorMast

- Vendor
- Vendor_Name

I'm hoping to use this new table to preload the Vendor and Vendor_Name values into slicers in my 200M row Direct Query model. VendorMast also has more than 1M rows by itself.

[v-tejrama · Accepted solution]

Hi @diablo9082 ,
Thanks for reaching out to the Microsoft fabric community forum.

You can create the table using LOOKUPVALUE, which is especially safe in DirectQuery and doesn’t depend on relationship direction. Here’s the DAX:

VendorTable =
ADDCOLUMNS (
    DISTINCT ( Payment[Vendor] ),
    "Vendor_Name",
    LOOKUPVALUE (
        VendorMast[Vendor_Name],
        VendorMast[Vendor], Payment[Vendor]
    )
)

This will return only the vendors that appear in the Payment table, along with their names from VendorMast. You can then use this new table in slicers.

I’ve attached a sample .pbix to demonstrate the setup, feel free to test or build on top of it!

Please find the attached pbix file for your reference.

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.

Best Regards,
Tejaswi.
Community Support

[diablo9082]

This worked phenomenally. Thanks!

[WishAskedSooner]

@diablo908 ,

It sounds like Payment is a fact table and Vendor is a dimension table. While you can certainly build dimension tables within PBI, data modeling should really be done in the data's native application (e.g. SQL Server/Oracle) especially since you are working with such large tables. Doing so is typically much easier/faster than DAX.

If you don't have access to the datasource, you can build the dimension table in Power Query. A simple enough select with joins should get folded anyway.