Skip to main content
cancel
Showing results for 
Search instead 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

Reply
corporate
Helper I
Helper I

Filter from measure in a table

Hi everyone,

I am new on Power BI and I'm trying to build a filter from a measure in a table. Here is my problem:

 

1) I import a table in Power BI with raw data: production values grouped by several columns (Age, Province...) 

input data.png

 

2) I create a table in Power BI adding up Production values grouped by Province, and this table reacts to page filters (Age, Region, Province...) selected by the users

table.png

 

3) I want to create a slice filter for Production values in the Power BI table: in this case, my filter should move between 3.620 and 8.100, and if I choose the highest value in the filter, Power BI table should show me only the first row.

When I change the other filters in the page, I want both my filter and my table to fit.

 

I have no idea if this is possible. Do you have any suggestion?

 

Thank you very much!

1 ACCEPTED SOLUTION
v-alq-msft
Community Support
Community Support

Hi, @corporate 

Based on your description, I created data to reproduce your scenario.

Table:

f1.png

 

Test (a calculated table):

 

 

Test = GENERATESERIES(0,10000,1)

 

 

 

You may create three measures as below.

 

 

Slicer Control = 
var t =
SUMMARIZE(
    'Table',
    'Table'[Region],
    'Table'[Province],
    "MeasureValue",CALCULATE([Measure])
)
return IF(
    COUNTROWS(
        FILTER(
            t,
            [MeasureValue]=SELECTEDVALUE(Test[Value])
        )
    )>0,
    1,
    -1
)

Table Control = 
IF([Measure] in DISTINCT('Test'[Value]),1,-1)

Measure = SUM('Table'[Production])

 

 

 

Then you need to put 'Slicer Control' in the visual level filter of 'Value' slicer and put 'Table Control' in the visual level filter of table visual.

f2.png

 

f3.png

 

Best Regards

Allan

 

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

View solution in original post

5 REPLIES 5
v-alq-msft
Community Support
Community Support

Hi, @corporate 

Based on your description, I created data to reproduce your scenario.

Table:

f1.png

 

Test (a calculated table):

 

 

Test = GENERATESERIES(0,10000,1)

 

 

 

You may create three measures as below.

 

 

Slicer Control = 
var t =
SUMMARIZE(
    'Table',
    'Table'[Region],
    'Table'[Province],
    "MeasureValue",CALCULATE([Measure])
)
return IF(
    COUNTROWS(
        FILTER(
            t,
            [MeasureValue]=SELECTEDVALUE(Test[Value])
        )
    )>0,
    1,
    -1
)

Table Control = 
IF([Measure] in DISTINCT('Test'[Value]),1,-1)

Measure = SUM('Table'[Production])

 

 

 

Then you need to put 'Slicer Control' in the visual level filter of 'Value' slicer and put 'Table Control' in the visual level filter of table visual.

f2.png

 

f3.png

 

Best Regards

Allan

 

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

Thank you very much @v-alq-msft !

parry2k
Super User
Super User

@corporate yes it can be achieved using visual level filter, create a what-if parameter with your range and then based on value selected in the what-if parameter, create a measure like below and use this following measure in visual filter by selecting where value is 1

Filter Measure = 
IF ( [Production Measure] >= [What if parmeter value], 1, 0 )

 

I would ? Kudos 🙂 if my solution helped. ?

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@corporate here is more detail on what-if parameter



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Thank you @parry2k , but unfortunately this is not  what I need, or I didn't understand how to use it.

From what I see, the what-if parameter must be created manually; I want a filter that automatically adjusts it's maximum and minimum values every time I change the filters on the page and the Production values in the Power BI table change.

 

Let me know if I haven't well explained my problem.

 

Thank you!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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