Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have seen other posts regarding this error also, but none of them could resolve the issue in my case, so here goes:
A certain product is produced through 5 processes. Depending upon the quality of finished goods required, the raw material can be used in low, normal, or high quantities. Based on a slicer for selecting the RM input level and a separate slicer for units produced of the product, the total raw material consumed to produce X products with low/normal/high raw material to be selected through slicer is required.
Input Level | Process | RM in Kgs |
Low | 1 | 10 |
Low | 2 | 12 |
Low | 3 | 14 |
Low | 4 | 16 |
Low | 5 | 18 |
Normal | 1 | 12 |
Normal | 2 | 14 |
Normal | 3 | 16 |
Normal | 4 | 18 |
Normal | 5 | 20 |
High | 1 | 14 |
High | 2 | 16 |
High | 3 | 18 |
High | 4 | 20 |
High | 5 | 22 |
Example: If "Low" RM Input level is selected, then each row of the low category should get multiplied with the selected units produced and divided by 50000 to get converted into bags.
If 5,000 units are selected and input level is low, answer should be = 7 bags
As seen in the screenshot, the DAX:
is giving error : A table of multiple values was supplied where a single value was expected.
kindly guide how to resolve this issue.
https://1drv.ms/u/s!AoqwAyWfotQsjmRi1tAHunMH0nH3?e=zR3AVp
Solved! Go to Solution.
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LeanAndPractise(Everyday) ) |
@ThxAlot In the same data above, the units produced selection needs to be aggregated everytime in each subsequent process and then get multiplied by the RM. For example, if "Input level" is "low" and "Units produced" selected are 5000, then:
10*5000 + 12*10000 + 14*15000 + 16*20000 + 18*20000.
Can you kindly guide how can the DAX be modified for this?
Glad to offer further help; but what's the logic for 10000, 15000, etc. ?
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LeanAndPractise(Everyday) ) |
Actually this is somewhat different scenario...the data is same as in the example given but number of units produced are taken to be aggregating with each process. i.e. In process 1, (if 5000 units selected) then 5000, in process 2, 10000, in process 3, 15000 and so on..
Thanks Alot @ThxAlot 🙂 the DAX is working exactly as intended but can you kindly guide how does the formula select the input level even though there's no mention of it in the DAX?
Aha, there's a mysterious power called "filtering" in the world of DAX; it's everywhere in any possible forms like slicer, column/row in table/matrix, legend of other visuals, etc.
Seriously, "filtering" is the essence of DAX. You don't need to explicitly refer to certain table/column in a measure as they already function through certain slicer/relationship. The more practise, the more elegant you'll find it.
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LeanAndPractise(Everyday) ) |
Lol 😁 Thanks for guiding as well as for the humour.
Hi @saqib1
try version v2 below
Total RM Consumed_v2 =
var new_x=SELECTEDVALUE('RM Input Level'[Input Level])))
var a = CALCULATE(VALUES('RM Input Level'[RM in Kgs]),
FILTER('RM Input Level','RM Input Level'[Input Level]= new_x
RETURN
SUMX('RM Input Level', a* 'Units Produced'[Units Produced Value])/ 50000
Proud to be a Super User!
Its still giving the same erorr.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
51 | |
27 | |
24 | |
13 | |
8 |
User | Count |
---|---|
74 | |
56 | |
47 | |
16 | |
12 |