Measures dependent on certain slicers

[seanmcc]

Hi all,

I am fairly new to Power BI and trying to create measures has been tricky, I have the following problem;

I have a set of slicers that allow me to select a drill. The data table provides the value for the selected drill.

The next step is to select two what if parameters - duration and sets.

As can be seen in my measure 'Drill 1 TD' I wish to calculate the value  from the selected drill (shown in table (average m.min)) and multiply it by the what if parameters. 

This works when only selections are made on the first set of slicers, but not if any other slicers are selected, for example drill 2 slicers.

Can I get the measures to work for their independent slicers?

I hope this makes sense?

Thank you for any help in advance.

Sean

[v-kelly-msft]

Hi  @seanmcc ,

Are all the data in slicers from different tables?If so,just create a measure as below:

Measure = SELECTEDVALUE('Table'[Column1])*SELECTEDVALUE('Table 2'[Column2])*SELECTEDVALUE('Table 3'[Column3])

And you will see:

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[seanmcc]

@v-kelly-msft 

My slicers for the drill category and drill are from the same table 'Drill Data'. Please see sample of this table below;

Name	Date	Day Name	Matchday (+/-)	Position Name	Session Type	Drill ID	Period Name	Total Duration	Total Distance (m)	Meterage Per Minute
Player 1	04/03/2019	Monday	MD -5	2. Full Back	Team Training		Attacking Drill	12	992	84.3
Player 2	04/03/2019	Monday	MD -5	5. Central Midfielder	Team Training		Attacking Drill	12	845	71.8
Player 3	04/03/2019	Monday	MD -5	4. Wide Midfielder	Team Training		Attacking Drill	12	867	73.7
Player 4	06/03/2019	Wednesday	MD -3	2. Full Back	Team Training		Attacking Drill	7	636	91.3
Player 5	06/03/2019	Wednesday	MD -3	5. Central Midfielder	Team Training		Attacking Drill	7	475	68.2
Player 6	06/03/2019	Wednesday	MD -3	6. Striker	Team Training		Attacking Drill	7	491	70.5
Player 7	20/03/2019	Wednesday	IB	2. Full Back	Team Training		1 vs 0 - S1	1	61	65.2
Player 1	20/03/2019	Wednesday	IB	2. Full Back	Team Training		1 vs 0 - S1	1	46	49.8
Player 7	20/03/2019	Wednesday	IB	5. Central Midfielder	Team Training		1 vs 0 - S1	1	59	63.3
Player 4	01/08/2019	Thursday	PS	2. Full Back	Team Training		Attacking Drill	19	1081	57.7
Player 5	01/08/2019	Thursday	PS	5. Central Midfielder	Team Training		Attacking Drill	19	1066	56.8
Player 6	01/08/2019	Thursday	PS	6. Striker	Team Training		Attacking Drill	19	866	46.2

I have managed to get the value I need from each group of slicers by modifying the interactions.

I will try step out how I got to this point and perhaps you can see if there is any issues.

1. I need to find the average of the drill selected, which works for me, I modified interactions to ensure the second set of slicers do not interact.

Drill AVG m.min = IF(ISFILTERED('Drill Data'[Period Name]),AVERAGEX('Drill Data',[Meterage Per Minute]),0)

2. I can now select the set duration and number of sets from my what if parameters. Which are used in my measure;

Drill 1 Est TD = [Drill AVG m.min]*[Drill 1 Sets Value]*[Drill 1 Duration Value]

Drill 2 Est TD = [Drill AVG m.min]*[Drill 2 Sets Value]*[Drill 2 Duration Value]

3. I need to sum these values together to get the total;

Drill Total = [Drill 1 Est TD]+[Drill 2 Est TD]

My expected result in this case should be 155 but it is returning Blank. I have turned off all interactions for this card. I thought this would be a straightforward sum but I must be doing something wrong somewhere along the way.

Thanks in advance

Sean

[v-kelly-msft · Accepted solution]

Hi @seanmcc，

If the 6 slicers are from the same table,and you need to sum the values from each 3 slicers ,you'd better create 6 separate tables for each slicer,otherwise it would make conflicts while filtering data.

Slicer is a kind of filter,even when you edit the interactions between visuals to none,when you calculate the sum values,it will check all the 6 slicers ,that is why you have values in each card,but have blank in total.

You can use below dax expression to create 6 separate tables:

table =DISTINCT('Table'[Column])

Then for the details,you can check the previous reply I made.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

@seanmcc , Explore edit interactions https://docs.microsoft.com/en-us/power-bi/service-reports-visual-interactions

https://radacad.com/bookmarks-and-buttons-making-power-bi-charts-even-more-interactive

[seanmcc]

Thanks @amitchandak for the reply.

Please see the image attached. I have made sure that there are no interactions between the slicers in first group with any other slicer groups. My measure 'Drill 1 TD' is displayed in a table on the right, however there is something wrong as there is no value.

I should be expecting to see a value of 179.74

89.87 (value in table below drill slicer) x 2 (number of sets) x 1 (duration of set).

Is the measure calculating the right thing?

Thanks

Sean