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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
nonononogin
New Member

FILTER function with a What-if Parameter

Hi! I'm quite new to Power Bi and really need help with this. Maybe a really simple one for most but here goes:

 

This works:

 

Table B = FILTER('Table A', [COMBINED_SCORE]< 10)
 
But this doesn't:
 
Table B = FILTER('Table A', [COMBINED_SCORE]< 'Table C'[Threshold Score Value])
 
where Threshold Score Value is the SELECTEDVALUE from the slicer of a What-if parameter. 
 
Hope someone can help me with this. Spent hours searching and testing but nothing works! 😣
Thank you in advance!
 
1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @nonononogin 
Calculated tables cannot read the filter context, therefore, this approach is not going to work.

Altenatively, create a compy of the table visual, then you can create a filter measure as follows, place it in the filter pane of the copy table and select "is not blank" then apply the filter

 

Filter Measure =
COUNTROWS (
    FILTER (
        'Table A',
        [COMBINED_SCORE] < AVERAGE ( 'Table C'[Threshold Score Value] )
    )
)

 

Note that I have used AVERAGE for aggregation in case the user selected multiple values then the [Combine_Score] measure will be compares to the average of the selected values. You can choose another aggregation like MAX or MIN based on your requirement or you can just use SELECTEDVALUE in which case the measure will be compared with BLANK in case of multiselection and the filter will therefore return all -ve results (In your case no results)

View solution in original post

3 REPLIES 3
nonononogin
New Member

Hi @tamerj1 

it didn't work. 

COUNTROWS gives the number of rows that fulfill the expression. So i didn't get any rows that are BLANKS in the measure. 

 

I was able to change how i create the visual managed to link it with the What-if parameter.   

Hi @nonononogin 
Not sure if this means you still need help. If you do please feel free to let me know.

tamerj1
Super User
Super User

Hi @nonononogin 
Calculated tables cannot read the filter context, therefore, this approach is not going to work.

Altenatively, create a compy of the table visual, then you can create a filter measure as follows, place it in the filter pane of the copy table and select "is not blank" then apply the filter

 

Filter Measure =
COUNTROWS (
    FILTER (
        'Table A',
        [COMBINED_SCORE] < AVERAGE ( 'Table C'[Threshold Score Value] )
    )
)

 

Note that I have used AVERAGE for aggregation in case the user selected multiple values then the [Combine_Score] measure will be compares to the average of the selected values. You can choose another aggregation like MAX or MIN based on your requirement or you can just use SELECTEDVALUE in which case the measure will be compared with BLANK in case of multiselection and the filter will therefore return all -ve results (In your case no results)

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.