Change measure based on number of selections in slicer

[Casperserven1]

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??

[tamerj1 · Accepted solution]

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

[WinterMist · Accepted solution]

@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

[Casperserven1]

@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

[WinterMist]

@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

[tamerj1]

@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. 

[WinterMist]

@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

[Mahesh0016]

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

[Casperserven1]

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

[tamerj1]

Hi @Casperserven1 
Please try

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

[Casperserven1]

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.

[tamerj1]

@Casperserven1 
1 x 22 = 22
2 x 22 = 44

3 x 22 = 66

It's the same. Have you tried?

[Casperserven1]

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.....

[tamerj1 · Accepted solution]

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

[Casperserven1]

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

[tamerj1]

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

[Casperserven1]

@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.