Using more than one what if parameter and aligning each one to a specific user selected value

[sg1234]

Hello, Thanks in advance for any help. 

I am trying to work out a system whereby a user can look up the ingredients and work out the volume of ingredients they require to make a widget.  My problem is that the user may want to override the volume of widgets to create (for a maximum of 3 different types of widget). 

I'll try to use some tables to explain my set up. 

Firstly I have Table 1 which is a list of all widgets, what goes into each widget and the percentage of each ingredient that is needed.  Secondly I have a Table 2 which contains the volume of each widget that we need to produce.  So I have a simple measure that calculates the volume of each ingredient needed.  

I also have a slicer which allows the user to select which widgets they want to be shown the data for.  

Now I have added 3 what-if-parameters which I want the user to use to override the volume of each / some of the widgets that they need to produce.  The resulting table should show the widget, each ingredient, each percentage, and the volume of each ingredient required after taking into account the user amended parameter.  

The problem I have is that the widget slicer filters the resulting table and so I can't work out how to align each widget to it's corresponding override volume.  My initial plan had been to get a list of the 3 widgets and then assign each widget to be the first, second or third widget, but in the table, each line is filtered and so each line only knows about one widget and not all 3. 

Any ideas?  Thanks!

I thought I would include the measures I've created and tried to use already

1. A measure containing a list of all of the user selected widgets:

PP_DISTINCT_WIDGETS =

IF(ISFILTERED(TABLE1[WIDGET]),

TRIM(CONCATENATEX(

    DISTINCT(TABLE1[WIDGET]),

    TABLE1[WIDGET], "¬ ",

    TABLE1[WIDGET])),"")

2. Some measures to try to identify each of the selected widgets.  Example 1 below.

PP_DIS_FIRST_WIDGET =

VAR end_1 = IF  (FIND("¬ ",[PP_DISTINCT_WIDGETS],,0) <> 0, FIND("¬ ",[PP_DISTINCT_WIDGETS],,0)-1, LEN([PP_DISTINCT_WIDGETS]))

VAR _1 = IF(end_1 = 0,

"Widget Selected #1: ",

CONCATENATE("Widget Selected #1: ",MID([PP_DISTINCT_WIDGETS],1,end_1)))

RETURN

_1

3. My attempt to try to calculate the volumes required (doesn't work as table filtered and so all widgets are assigned to widget #1

PP_Component_Vol_3_Mix =

var _1 =

IF(

                     AND(MAX(TABLE1[WIDGET]) = [PP_DIS_FIRST_WIDGET], AND(   [Volume Required Value] <> BLANK() ,[Volume Required Value] <> 0 )),

                     [Volume Required Value]*(Sum( TABLE1[PERCENT])/100),

                       [Required Volume Item]*(Sum(TABLE1[PERCENT])/100))

var _2 =

IF(

                     AND(MAX(TABLE1[WIDGET]) = [PP_DIS_SECOND_WIDGET], AND(   [Volume Required 2 Value] <> BLANK() ,[Volume Required 2 Value] <> 0 )),

                     [Volume Required 2 Value]*(Sum( TABLE1[PERCENT])/100),

                       [Required Volume Item]*(Sum(TABLE1[PERCENT])/100))

var _3 =

IF(

                     AND(MAX(TABLE1[WIDGET]) = [PP_DIS_THIRD_WIDGET], AND(   [Volume Required 3 Value] <> BLANK() ,[Volume Required 3 Value] <> 0 /)),

                     [Volume Required 3 Value]*(Sum( TABLE1[PERCENT])/100),

                      [Required Volume Item]*(Sum(TABLE1[PERCENT])/100))                      

return

               _1 + _2 + _3

[Anonymous]

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.

[sg1234]

Thanks 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!

[Anonymous]

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.

[sg1234 · Accepted solution]

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!

[sg1234]

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