cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## linking slicers' preassigned values to DAX

Can you kindly guide on this query? A certain product can be made through different amounts of input depending upon several factors. DAX is needed to ascertain the amount of input required to produce the product based on the influencing factors. What’s required is that the user selects options from the different factors through slicers , and the DAX calculates the input required according to preassigned values of each factor which are multiplied by the standard amount required to produce one unit.

A =  1    B = 0.95       C = 0.85

Location

D = 1 E = 0.95 F = 0.85

Plant Efficiency

G = 1 H = 0.95  I = 0.85

Example:

Standard is 100 kg required to produce one unit of output.

But based on business unit A, Location E, Plant efficiency I, the actual input should be 100 * 1*0.95*  0.85 = 80.75 kg.

Slicers for Business unit type, Location, and Plant efficiency are needed such that by selecting a specific option from each slicer, the resultant value gets multiplied by the standard input required per unit.

Appreciate your time and guidance 🙂

2 ACCEPTED SOLUTIONS
Super User

see attached

Super User

``````RM =
var a =[T Start Value]
var b = [T Start Value]+[T 1 Value]+[T 2 Value]
var c = CALCULATE(sum('Days n Kgs'[kilograms]),'Days n Kgs'[Days]>=a,'Days n Kgs'[Days]<=b)

Your other requirements (slicer dependencies) cannot be achieved since you have no meaningful data model.

10 REPLIES 10
Super User

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

Helper II

@lbendlin  Thanks alot for responding 🙂

Following are the tables regarding the data:

 Business Unit Type RM reqd A 1 B 0.95 C 0.85

 Plant EffIciency RM reqd G 1 H 0.95 I 0.85

 Location RM reqd D 1 E 0.95 F 0.85

One unit of the product needs 100 kg Raw material to be produced (as a standard). But the three factors above, namely Business unit type, location, and Plant Efficiency can alter the amount of RM required to produce one unit of output.

Expected Outcome:

Slicers are needed for each of the above factors. When a user selects an option from the slicers (A-I), the corresponding amount of RM reqd column of that particular factor should get multiplied with the standard amount of RM reqd for producing one unit of output.

Example:

If Business Unit type = B

Plant Efficiency = I

Location = F

then, Raw material required should be = 100*0.95*0.85*0.85 = 68.64 kg

The DAX for this calculation is requested.

Super User

see attached

Helper II

@lbendlin Thanks for your time. Actually, there's a bit of customization needed. The kilograms column is related to 'Days' column:

 Days kilograms 10 1 11 2 12 3 13 4 14 5 15 6 16 7 17 8 18 9 19 10

There are two distinct periods which need to be selected to know the amount of Raw material required to produce the output. What’s needed is that three more slicers give the user option to choose the starting days and the two time periods. And the corresponding values from Kilograms column get added up and multiplied to the factors weightages mentioned in previous question.

Expected Outcome:

One slicer (Start) of starting day e.g 11th day to start. Two slicers (T1 & T2) for two distinct time periods. Say for e.g. one slicer has period selected of 3 days and the other has selection of 4 days. So if user selects Start slicer as 11, T1 = 3, T2 = 4, and the Business unit type = B, Location = F, Plant Efficiency  =I, then outcome should be as follows:

Kilograms corresponding to Start (slicer), 11th day = 2

T1+T2= 3+4= 7, therefore kilograms of 7 more days will have to be added = 2+(3+4+5+6+7+8+9)= 44 Kilograms

Factor weightages:

B= 0.95

F= 0.85

I=  0.85

End Result = 44 * 0.95*0.85*0.85 = 30.2 kgs

The modified DAX for the above calculation is requested.

Helper II

really appreciate it if someone could guide on the revised query please...

Super User

Please show the expected outcome based on the sample data you provided.

Helper II

@lbendlin can you kindly share your email? I'm unable to attach (or drag) the pbix file in the reply section. it says "the file type (.pbix) is not supported"

Super User
Helper II

@lbendlin Kindly see the attached link

Raw material consumption needs to be determined such that when a user selects the slicer "T Start", the Kilograms column corresponding to the days number of that selection gets selected. Then Only after T Start selection, T1 can be selecetd and then T2 can be selected.

One more issue that needs to be solved is that once I select an option from Business unit, location, or, plant efficiency, the other options (eg D,E,F,G,H,I etc disappear).

Super User

``````RM =
var a =[T Start Value]
var b = [T Start Value]+[T 1 Value]+[T 2 Value]
var c = CALCULATE(sum('Days n Kgs'[kilograms]),'Days n Kgs'[Days]>=a,'Days n Kgs'[Days]<=b)

Your other requirements (slicer dependencies) cannot be achieved since you have no meaningful data model.