cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Change measure based on number of selections in slicer

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

2 ACCEPTED SOLUTIONS
Super User

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

Impactful Individual

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

15 REPLIES 15
Impactful Individual

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

Helper I

@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
Impactful Individual

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

Super User

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.

Impactful Individual

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

Super User

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

Helper I

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

Super User

Hi @Casperserven1

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

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.

Super User

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

3 x 22 = 66

It's the same. Have you tried?

Helper I

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

Super User

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

Helper I

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

Super User

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

Helper I

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.