New column

Super User

1 year ago

Hi sportive92003 ,

To address the requirement of dynamically assigning vendors without creating a new column and to make the logic easy to maintain, you can implement a solution using a separate mapping table and a DAX measure. The mapping table will store the rules for assigning vendors based on specific conditions such as no_client, Month&Year, and Product. This table can be created manually in Power BI or imported from an external source. For instance, the table could include the following columns: no_client, Month&Year, Product, and Vendor, with rows like (123456, 12-2024, ABC, B), (123456, 12-2024, DEF, B), and (123456, 12-2024, GHI, B).

After creating the mapping table, you can write a DAX measure to dynamically determine the vendor. The measure would look up the appropriate vendor based on the current conditions in your visuals, using the mapping table. The DAX code for the measure could be as follows:

Dynamic Vendor = 
VAR CurrentMonthYear = FORMAT(TODAY(), "MM-yyyy") // Replace TODAY() with a fixed date filter if needed
VAR CurrentNoClient = SELECTEDVALUE(Sales[no_client])
VAR CurrentProduct = SELECTEDVALUE(Sales[product])

VAR AssignedVendor = 
    LOOKUPVALUE(
        'Vendor Rules'[Vendor],
        'Vendor Rules'[no_client], CurrentNoClient,
        'Vendor Rules'[Month&Year], CurrentMonthYear,
        'Vendor Rules'[Product], CurrentProduct,
        "A" // Default to Vendor A if no match is found
    )

RETURN 
    AssignedVendor

This measure works by using the LOOKUPVALUE function to find a matching rule in the Vendor Rules table for the current no_client, Month&Year, and Product. If a match is found, the corresponding vendor is returned. If no match is found, the measure defaults to "A."

Once the measure is created, you can use it in your visuals instead of the original Vendor column. This way, the vendor name displayed in the visuals dynamically adjusts based on the defined rules in the mapping table. To add more rules in the future, you simply update the mapping table with new rows, such as adding rules for new clients, months, or products. The measure will automatically reference the updated table without requiring changes to the DAX logic, making it a scalable and maintainable solution. This approach keeps your data model clean and ensures your visuals remain consistent without significant manual intervention.

Best regards,

Forum Discussion