New column

[sportive92003]

Hello,

I have my Sales database (no_client, product, amount, etc...) and a Clients database, where the name of the vendor (A,B,C,D,E) is indicated for each no_client of the Sales database. Normally for Client 123456, the vendor is A. But something happend, now I have to program another vendor depending of the product. Programmation should say:

For Month&Year=12-2024, and no_client=123456 and products=ABC or product=DEF or product=GHI, then vendor = B, else vendor A.

How to program it?

And is it possible to do not create a new column of vendor and using the actual Vendor column to change the name of the vendor depending of this programmation? If a create a new column I will have to change all my graphics and boards in each pages to use the new vendor variable and I didn't want it. Or just if it's really necessary. In the futur, I will have to add the same thing for another no_client and product x or y or z and put the right vendor.

Thanks!

[Greg_Deckler · Accepted solution]

@sportive92003 Try:

if [Month&Year] = "12-2024" and [no_client] = 123456 and ( [products] = "ABC" or [products] = "DEF" or [products] = "GHI" ) then "B" else "A"

[DataNinja777 · Accepted solution]

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,

Best regards,

[dufoq3 · Accepted solution]

Hi @sportive92003, you should allways provide sample data and expected result!

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRS0lEyMjAyAVKGRsYmpmZAhqOTM4hUitUBKjAgpACbCS6ublgVWFqYm5mCGO4envhNgCuIBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, Year = _t, no_client = _t, product = _t, vendor = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Month", Int64.Type}, {"Year", Int64.Type}, {"no_client", Int64.Type}}),
    ReplacedVendor = Table.FromRecords(Table.TransformRows(ChangedType, each _ & 
        ( if Text.Combine({Text.From([Month]), Text.From([Year])}, "-") = "12-2024" and [no_client] = 123456 and List.Contains({"ABC", "DEF", "GHI"}, [product])
          then [vendor = "B"] else [] )
    ), Value.Type(Table.FirstN(ChangedType, 0)))
in
    ReplacedVendor

[DataNinja777 · Accepted solution]

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,

Best regards,

[Greg_Deckler · Accepted solution]

@sportive92003 Try:

if [Month&Year] = "12-2024" and [no_client] = 123456 and ( [products] = "ABC" or [products] = "DEF" or [products] = "GHI" ) then "B" else "A"