Forum Discussion

diablo9082's avatar
diablo9082
Regular Visitor
1 year ago
Solved

Combine distinct with lookup

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 Ve...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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

     

WishAskedSooner's avatar
WishAskedSooner
Icon for Continued Contributor rankContinued Contributor
1 year ago

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.