topic Re: Change measure based on number of selections in slicer in DAX Commands and Tips

Change measure based on number of selections in slicer

Casperserven1 — Mon, 26 Jun 2023 12:58:16 GMT

Hi - I have a measure which I want to change depending on how many selections are made in a slicer.

e.g.

I select 1 of 4 selections in my slicer then my value is 22 + [sum of something]

I select 2 of 4 selections in my slicer then my value is 44 + [sum of something]

I select 3 of 4 selections in my slicer then my value is 66 + [sum of something] etc...

I have tried something like IF( COUNT( ISFILTERED( FinalData[Table] ) = 1 , 22 , iF ETC..

This will not work as count function only counts on table name etc.

Any one any ideas??

Re: Change measure based on number of selections in slicer

tamerj1 — Mon, 26 Jun 2023 13:04:44 GMT

Hi Casperserven1
Please try

= 22 * COUNTROWS ( ALLSELECTED ( FinalData[Table] ) ) + [sum of something]

Re: Change measure based on number of selections in slicer

Mahesh0016 — Mon, 26 Jun 2023 13:06:09 GMT

Casperserven1 please try below dax

something like =
VAR CNT = COUNT( ISFILTERED( FinalData[Table] )
RETURN

SWITCH ( CNT , 1, 22,
CNT, 2, 44,
CNT, 3 , 66,

CNT , 4 , 88 )

Casperserven1 THANK YOU!!

Re: Change measure based on number of selections in slicer

Casperserven1 — Mon, 26 Jun 2023 13:18:20 GMT

Hi Mahesh - thanks for replying - unfortunately you canmot put COUNT in front of ISFILTERED - it won't work.

Re: Change measure based on number of selections in slicer

Casperserven1 — Mon, 26 Jun 2023 13:21:10 GMT

Thanks tamerj1 - unfortunately this wil not work. The logic is that if there is 1 selection (in the same slicer) then add 22 to the measure, if there are 2 slicer selections (in the same slicer) than add 44. Your suggestion has multiply "*" which is not what is needed.

Re: Change measure based on number of selections in slicer

tamerj1 — Mon, 26 Jun 2023 13:44:53 GMT

Casperserven1
1 x 22 = 22
2 x 22 = 44

3 x 22 = 66

It's the same. Have you tried?

Re: Change measure based on number of selections in slicer

Casperserven1 — Mon, 26 Jun 2023 13:47:26 GMT

Yes but 22 multiplying by the measure is not the same as 22 adding to the measure - remember this is 22 + measure and 44 + measure so in this case 22 * measure is not the same and 44 * measure is not the same.....

Re: Change measure based on number of selections in slicer

WinterMist — Mon, 26 Jun 2023 14:45:48 GMT

Casperserven1

I am confused about your last response to @tamerj1.

"...remember this is 22 + measure and 44 + measure..."

When I do what he is recommending, the result is exactly 22 + measure & 44 + measure.

1 Slicer value) 10 + 22 = 32

2 Slicer values) 30 + 44 = 74

3 Slicer values) 60 + 66 = 126

It works for me no problem.

The only difference here is that if all 4 are selected, it adds 88, but you can remove this by checking to see if more than 3 values are selected.

If it's still not working, perhaps you can provide more detail?

Regards,

Nathan

Re: Change measure based on number of selections in slicer

tamerj1 — Mon, 26 Jun 2023 14:55:50 GMT

Casperserven1
But as per my code the measure is added not multiplied!!

Re: Change measure based on number of selections in slicer

WinterMist — Mon, 26 Jun 2023 15:32:34 GMT

Casperserven1

Here's the same solution with the limit check of 3 slicer values selected. With this logic, the measure will only return a value if 3 or less slicer values are selected.

Regards,

Nathan

Re: Change measure based on number of selections in slicer

Casperserven1 — Mon, 26 Jun 2023 15:55:55 GMT

Ok so lets assume that [sum of something] is 42. If you multiply 22 * 42 = 924. If you add 22 + 42 = 64

Re: Change measure based on number of selections in slicer

tamerj1 — Mon, 26 Jun 2023 16:00:50 GMT

Casperserven1
22 x number of selections (let's say 2) + 42
= 22 x 2 + 42
= 44 + 42
= 82

Re: Change measure based on number of selections in slicer

Casperserven1 — Tue, 27 Jun 2023 13:11:38 GMT

tamerj1 - thanks - will go away and try - this assumes of course that multiples of 22 are consistent where 1 selection might be 22 but 2 selections might be 75 and three selections might be 43 for instance ... but I'll go away and try out the suggestions above - thanks again.

Re: Change measure based on number of selections in slicer

Casperserven1 — Tue, 27 Jun 2023 13:50:30 GMT

WinterMist - Nathan - many thanks - this solution works. Had to tweak it to make it work with my measure but all is good. Here is the entire measure I used incorporating most of your solution:

WorkforceAll =

VAR Multiplier = 22

VAR NumSelected = COUNTROWS( ALLSELECTED( FinalData[Table] ) )

VAR Productz = Multiplier * NumSelected

VAR Limit = IF( NumSelected <=5, Productz )

VAR StartingNumber = Limit

VAR CurrentTurn =

SELECTEDVALUE ( FinalData[Turn] )

VAR CumulativeChanges =

CALCULATE (

IBMeasures[NHminusRetirees],

ALLEXCEPT( FinalData, FinalData[Table], FinalData[Turn], FinalData[CourseDate] ),

FinalData[Turn] <= CurrentTurn

)

VAR Result = StartingNumber + CumulativeChanges

RETURN

Result

Re: Change measure based on number of selections in slicer

WinterMist — Tue, 27 Jun 2023 14:18:26 GMT

Casperserven1

I'm glad this was helpful to you; but all I did was take the solution from tamerj1 & seperate it into VARs; for readability & easier debugging.

If you want to become good at DAX, follow tamerj1 .

Study all his solutions.

After [Russo & Ferrari], tamerj1 is #2 in my book.

Regards,

Nathan

Re: Change measure based on number of selections in slicer

tamerj1 — Tue, 27 Jun 2023 16:55:35 GMT

WinterMist

Wow! I feel flattered. I need to be realistic and admit that It will be a long journey to reach to the point where you have placed me. Rosso and Ferrari are incomparable and they won't be for a very long time.