Apply Filter Before Calculation

[mattterriault]

The formula I have below works but not when I do a filter. I am trying to get the lowest value in a group and assign a 1 to it and 0 when it is not the lowest value. Can I add a If filtered or something to this column formula to make it look for the lowest of all the ones filtered not the entie table. Thanks for any help!

A = dataset

B = The score by row at the aggregated view desired

C = after the formuala is applied that works but, If I were to take out, for example Company B, Distance "Far" , Size "B" , it would keep all the other companys at zero because the formula is still looking over the entire dataset and for some reason is not looking at the company filter I have in place.

D = What I am hoping it to look like

the formula calculating this is:

Column = 
var _minvalue = 
MINX(
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Size]=EARLIER('Table'[Size])
        &&'Table'[Distance]=EARLIER('Table'[Distance])),
        'Table'[score])
return
IF(
    'Table'[score] = _minvalue,1,0)

[DataInsights]

@mattterriault,

Try this measure. The reason that your calculated column did not work properly is that calculated columns are not affected by filter context.

Lowest Value =
VAR vScore =
    MAX ( Table[Score] )
VAR vGroupMinValue =
    CALCULATE ( MIN ( Table[Score] ), ALLSELECTED ( Table[Company] ) )
VAR vResult =
    IF ( vScore = vGroupMinValue, 1, 0 )
RETURN
    vResult

[mattterriault]

Thank you for responding. This works but it does not roll it up like the column calculation. The ultimate goal is to say " out of what is selected, how many times was company A the lowest, how many times was company B the lowest, etc....". I would like to not even have distance and size in the view, just the sum of the calculation you provided. Thanks again, I know I didnt clarify all that at the beginning.

[v-yiruan-msft · Accepted solution]

Hi @mattterriault ,

Please create another new measure with below formula to replace the measure [Lowest Value] on the matrix:

Measure =
SUMX (
    VALUES ( 'Table'[Distance] ),
    SUMX ( VALUES ( 'Table'[Distance] ), [Lowest Value] )
)

Or you can refer the method to fix the incorrect total value problem:

Power BI: Totals Incorrect

Fixing Incorrect Measure Totals

Why Your Total Is Incorrect In Power BI

Best Regards
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.