cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

Solution Sage

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

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors