Forum Discussion
Using more than one what if parameter and aligning each one to a specific user selected value
- 1 year ago
Hi Jarvis,
Many thanks for all your help with this.Unfortunately, the main problem is how to have the results in one table, but after some more messing around, I've hit upon a solution that seems to work for our set up. So I have created three copies of our Table 2 but only containing a list of all of the possible widgets. There are no relationships between the 3 new tables and any other tables. I've then created 3 slicers, one for each of the tables, and the user can select a single widget from each slicer. Then I use the following measures to grab the id of the selected widgets and to filter the main Table 1 so it only includes the selected widgets.
So 3 versions of each of the two measures below:
W_NUM_SELECTION_1 = IF(ISFILTERED(WIDGET_SELECTION_1),CALCULATE(MAX(WIDGET _SELECTION_1[WIDGET])),"")IngredientVolume1 =
SUMX (
FILTER('TABLE 1', 'TABLE 1'[WIDGET] = [W_NUM_SELECTION_1]),
'TABLE 1'[PERCENT] * SELECTEDVALUE('Volume Required'[Volume Required]) / 100
)
Then
Ingredients_All = [IngredientVolume1]+[IngredientVolume2]+[IngredientVolume3]
And the following measure to make sure that only the selected rows and included in the table
ID_3_Mix = IF(
AND(ISFILTERED(WIDGET_SELECTION_1[WIDGET]),
MAX(TABLE1[WIDGET]) = [W_NUM_SELECTION_1] ||
MAX(TABLE1[WIDGET]) = [W_NUM_SELECTION_2] ||
MAX(TABLE1[WIDGET]) = [W_NUM_SELECTION_3]), 1,0)
Hope that helps someone. Or my future self when I forget how I did it previously!
Thanks again Jarvis for all your help!
Pasting the above excel cells so you can see what I'm talking about!
| Table 1 | Table 2 | ||||||||||||
| Widgets | Ingredients | Percent | Required Vol Total | Required Volume Item (Measure) | Mix | Vol | Widget Select Slicer | Volume Parameter 1 | Volume Parameter 2 | Volume Parameter 3 | |||
| 1 | a | 75 | 200 | 375 | 1 | 200 | 1,2,3 | 500 | 1000 | ||||
| 1 | b | 10 | 200 | 50 | 2 | 500 | |||||||
| 1 | c | 15 | 200 | 75 | 3 | 400 | |||||||
| 2 | a | 80 | 500 | 800 | 4 | 100 | |||||||
| 2 | d | 10 | 500 | 100 | |||||||||
| 2 | e | 10 | 500 | 100 | |||||||||
| 3 | f | 20 | 400 | 80 | |||||||||
| 3 | b | 20 | 400 | 80 | |||||||||
| 3 | c | 60 | 400 | 240 | |||||||||
| 4 | a | 10 | 100 | This row shouldn't be in the resulting table as the widget hasn't been selected | |||||||||
| 4 | f | 10 | 100 | This row shouldn't be in the resulting table as the widget hasn't been selected | |||||||||
| 4 | g | 80 | 100 | This row shouldn't be in the resulting table as the widget hasn't been selected |