Forum Discussion
Apply Filter Before Calculation
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)
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:
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.
3 Replies
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 vResultThank 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.
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:
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.
