Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
burrito
Helper I
Helper I

How to make a slicer with variables to be used in other column formulas?

Need help on below concern. I have below measure formula and is working well however I want to change a part of it.

 

Measure=
      CALCULATE(
      SUM(Table1[values]),
      FILTER(
             ALL(Table2),
             Table2[%values]>0.90
)
)

 

I want to make a slicer from 10 to 100% and whatever is chosen from that slicer would be used as the value for ">" instead of having a fixed value of 0.90.

 

Any idea how I can make this slicer and use whatever is set to be the value used instead of 0.90.

1 ACCEPTED SOLUTION
selimovd
Super User
Super User

Hey @burrito ,

 

you should create a new and not connected table with the percentages that you want to use in the slicer.

This column you use as a slicer. Then you can use SELECTEDVALUE to get the value from the slicer.

 

The adapted function would look like this:

Measure =
VAR _SelectedSlicer = SELECTEDVALUE ( SlicerTable[Percentage], 0.9 )
RETURN
    CALCULATE (
        SUM ( Table1[values] ),
        FILTER ( ALL ( Table2 ), Table2[%values] > _SelectedSlicer )
    )

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic

View solution in original post

5 REPLIES 5
v-xuxinyi-msft
Community Support
Community Support

Hi , @burrito 

 

Firstly, you can create a calculated table as follows.

 

slicer table = VALUES('Table'[%values])

 

vxuxinyimsft_0-1701654956399.png

 

And you can create a measure as follows.

 

>selectedvalue = IF(SELECTEDVALUE('Table'[%values]) > SELECTEDVALUE('slicer table'[%values]), 1 , 0)

 

 

Then put the measure into the filter so that the visual only shows data where the measure is equal to 1.

vxuxinyimsft_1-1701655042376.png

 

 

vxuxinyimsft_2-1701655068267.png

Is this the result you expect?

 

 

Best Regards,

Yuliax

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

selimovd
Super User
Super User

Hey @burrito ,

 

you should create a new and not connected table with the percentages that you want to use in the slicer.

This column you use as a slicer. Then you can use SELECTEDVALUE to get the value from the slicer.

 

The adapted function would look like this:

Measure =
VAR _SelectedSlicer = SELECTEDVALUE ( SlicerTable[Percentage], 0.9 )
RETURN
    CALCULATE (
        SUM ( Table1[values] ),
        FILTER ( ALL ( Table2 ), Table2[%values] > _SelectedSlicer )
    )

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic

Hi @selimovd , I tried your recommendation and it still gave me what I got from my previous formula. I created a new table with 1 column only, I put 10-100%. I then created the function you suggested to use selected value. However, the data on the new measure still produce the same result even after adjusting the slicer in the visual. 

 

Must the 0.9 be retained for below formula?

burrito_0-1701431628185.png

Hey @burrito ,

 

no, the "0.9" in SELECTEDVALUE is the alternative result, in case you didn't choose anything in the slicer.

Can you share a demo file? Usually it should work.

 

Best regards

Denis

Hi, it now works. I refreshed the report.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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