power bi dax modifying selectedvalue of a slicer

Question

I am new at powerbi dax and I would be very grateful if someone could help with this.

I have a sales table with all the sales, material id, quantities, customer id and delivery date, a customer table with customer ID and contract table with material id, agreed prices, period of time of agreement and customer ID.

As usual, customers place any order but those with a contract for a particular material have a special prices during a period of time. 97% of customers with contract has same customer id contract and sales tables, but there is a 3% of customers with contract that they have other partners (customer ID are not present in contract table) also placing orders under the same contract. I have managed to create a matrix by contract with all the quantity, sales and delivery date for all the materials in the contract. I am having problems to include in the matrix those sales under the contract made by the partners since I am not able to properly modify the selectedvalue of the slicer or of the row in the contract matrix when drillingthough.

I think there should be something like (maybe in a variable):
if selectedvalue(customerID)= "1" then consider customerID = OR(20,33,45,1); if selectedvalue(customer id)= "13" then consider customerID = or(72,42,63); else do not modify selectedvalue(customerID) Apply the original or modified selectedvalue of the previous conditional function to the measure calculating total sales, quantities in a particular period of time.

Thanks a lot.

OscarLar OscarLar · Accepted Answer · 2021-05-12T06:58:07

This can possibly be solved by using a SWITCH function, something like this:

Measure = 
var selVal = SELECTEDVALUE(customerID)

return
SWITCH(
    TRUE(),
    selVal in {"1", "20", "33", "45"}, CALCULATE( EXPRESSION, customerID in {"1", "20", "33", "45"} ),
    selVal in {"13", "42", "63", "72"}, CALCULATE( EXPRESSION, customerID in {"13", "42", "63", "72"} ),
    CALCULATE( EXPRESSION )
)

Depending on what your model looks like there's always a better way to do it. But this should work based on my guess of how your data table looks. Next time please provide a sample data and a model description.

power bi dax modifying selectedvalue of a slicer

2 Answers