Calculating DAX based of table row

[Anonymous]

Hello,

I want to create a DAX that will execute a expression based on the table rows. My measure is 

Over Spec = CALCULATE( COUNTROWS( FILTER((Infinity), [Value] > 3.6)) , Infinity[Test] = "Shoulder Weight") + CALCULATE( COUNTROWS( FILTER((Infinity), [Value] > 1.4)) , Infinity[Test] = "Body Weight")

Only Shoulder Weight and Body Weight should have values when evaluating this measure. 

Thanks

[mahoneypat]

Your calculate filter on Infinity[Test] is overwriting the filter provided by the row on the table visual.  Wrap it in

KEEPFILTERS() to get your desired effect.

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

This is the error I get when wrapping measure in KeepFilters 

KEEPFILTERS function can only be used as a top level filter argument of CALCULATE and CALCULATETABLE or with a table argument of a function performing a table scan.

[mahoneypat · Accepted solution]

Please see this post to learn more about KEEPFILTERS().

https://www.sqlbi.com/articles/using-keepfilters-in-dax/

One of the example in that article shows the syntax like below.  Is that how you used it?

OnlyRed :=

CALCULATE (

    [Sales Amount],

    KEEPFILTERS ( Products[Color] = "Red" )

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[az38]

Hi @Anonymous 

try more easy

Over Spec = 
CALCULATE( COUNTROWS(Infinity), Infinity[Value] > 3.6, Infinity[Test] = "Shoulder Weight")
 + 
CALCULATE( COUNTROWS(Infinity), Infinity[Value] > 1.4, Infinity[Test] = "Body Weight")

[Anonymous]

The result using that measure is 

I need the over column to calculate based on test column