Skip to main content
cancel
Showing results for 
Search instead 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

Reply
saqib1
Helper II
Helper II

linking slicers' preassigned values to DAX

@andhiii079845 

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.

 

Business unit type

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

 

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)
return c*SELECTEDVALUE('Business Units'[RM])*SELECTEDVALUE('Location Weightage'[RM])*SELECTEDVALUE('Plant Efficiency'[RM])

 

lbendlin_0-1685150824467.png

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

View solution in original post

10 REPLIES 10
lbendlin
Super User
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.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

@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.  

see attached

@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.

 

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

Please provide sanitized sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

@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"

@lbendlin Kindly see the attached link

 

https://1drv.ms/u/s!AoqwAyWfotQsjGyIi3BStwNfDVN2?e=teJilI 

 

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).

 

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)
return c*SELECTEDVALUE('Business Units'[RM])*SELECTEDVALUE('Location Weightage'[RM])*SELECTEDVALUE('Plant Efficiency'[RM])

 

lbendlin_0-1685150824467.png

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.