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
As you have shown in the pbix file, it is difficult to dynamically identify the selection of widgets and calculate the corresponding volume parameters in the same table visual.
However, you can use three slicers and three table visuals and perform editing interactions to achieve dynamic selection. Please check if the following steps meet your needs.
1. Create three volume parameters as before, then create two auxiliary columns in Table1, like this:
Widgets 2 = 'Table 1'[Widgets]
2. Create three slicers with Table 1[Widgets], Table 1[Widgets 2] and Table 1[Widgets 3], and then update the IngredientVolume measures.
IngredientVolume1 =
SUMX(
FILTER('Table 1', 'Table 1'[Widgets] = SELECTEDVALUE('Table 1'[Widgets])),
('Table 1'[Percent] / 100) * 'Volume Parameter 1'[Volume Parameter 1 Value]
)
3. Create a table visual with Table 1[Widgets], Table 1[Ingredients], Table 1[Percent], [IngredientsVolume1]. Create two more table visuals in the same way and replace the corresponding fields with Table 1[Widgets 2],[IngredientsVolume2] and Table 1[Widgets 3],[IngredientsVolume3].
4. Click on the Widgets slicer, select “Edit interactions” in the Format tab, and uncheck the Widgets slicer’s filter for the Widgets 2 slicer, Widgets 3 slicer and their corresponding table visuals. Do the same for the other two slicers.
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 IHi 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!