Forum Discussion

nonononogin's avatar
nonononogin
New Member
3 years ago
Solved

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!
 
  • tamerj1's avatar
    tamerj1
    3 years ago

    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)

3 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    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)

  • nonononogin's avatar
    nonononogin
    New Member
    3 years ago

    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.   

    • tamerj1's avatar
      tamerj1
      Community Champion
      3 years ago

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