Forum Discussion
Helper IUsing more than one what if parameter and aligning each one to a specific user selected value
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!
Hi sg1234
Please try the following possible solutions:
1. Create three Numeric Range Parameters
2. Create the following measures to calculate the volume of each ingredient
IngredientVolume1 =
SUMX (
FILTER('Table 1', 'Table 1'[Widgets] = 1),
'Table 1'[Percent] * SELECTEDVALUE('Volume Parameter 1'[Volume Parameter 1]) / 100
)IngredientVolume2 =
SUMX (
FILTER('Table 1', 'Table 1'[Widgets] = 2),
'Table 1'[Percent] * SELECTEDVALUE('Volume Parameter 2'[Volume Parameter 2]) / 100
)IngredientVolume3 =
SUMX (
FILTER('Table 1', 'Table 1'[Widgets] = 3),
'Table 1'[Percent] * SELECTEDVALUE('Volume Parameter 3'[Volume Parameter 3]) / 100
)3. Create a table visual, put Table1[Widgets],Table1[Ingredients],Table1[Percent] and the new measures into it, and create a slicer with Table1[Widgets].
Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper IThanks so much for your time, Jarvis, that's helpful and works except I don't want to have to hard code the widget number for each of the three selected widgets.
I want something like
IngredientVolume1 =
SUMX (
FILTER('Table 1', 'Table 1'[Widgets] = [MEASURE_CONTAINING_WIDGET_1_ID]),
'Table 1'[Percent] * SELECTEDVALUE('Volume Parameter 1'[Volume Parameter 1]) / 100
)
In my situation the user could select any three out of thousands of possibilities, so somehow I have to dynamically have a measure that can identify the three selected widgets and assign them to be the first, second or third widget. This is the bit that I can't get to work is the table, as the table has a filter so if I create measure that have the details of the three selected widgets it always assigns widget 1 to be the widget that corresponds to the table row and the other 2 are blank. I have tried to show what I mean in the file I've saved on my google drive (which hopefully you can see) but will add a screenshot too.
https://drive.google.com/file/d/1lFBSt6NkO1kp9DeWad9nR1iIWXmjigqQ/view?usp=sharing
Thanks again for your help - it's very much appreciated!