Forum Discussion
Measure formula using SUMX, FILTER and MAX/MIN
Hey Herbet, thank you for your feedback. I appreciate it as it helps me better understand how the DAX language is functioning under the hood.
While I cannot dispute your solution provides the required results I still have concerns. While I cannot dispute your solution provides the required results I still have concerns.
The programmer in me screams loudly at the repetative nature trying to force the language to do what I thought it should be doing in the first place. To me it feels like a calculator that does not correctly multiple two numbers so the solution is to just solve it using addition (imagine calculating 4027 * 1024 in such a way).
I was under the impression that the SUMX(<table>, <expression>) function would evaluate the <expression> using the provided <table> much the same way that the CALCULATE(<expression>,<filter1>,<filter2>…) function does.
Knowing this now I could reduce the context going into SUMX() and the only way to do that would be to leverage CALCULATE(). The reason I would prefer this method is when you get into complicated filters in real world examples that start to create very complex formulas.
MeasureMax = CALCULATE( SUMX( Table1 , MAX([Value]) ) , FILTER(Table1, Table1[Category] = "c") )
MeasureMin = CALCULATE( SUMX( Table1 , MIN([Value]) ) , FILTER(Table1, Table1[Category] = "c") )
Maybe I have misunderstood how the <expression> is being handled in terms of column based functions, I wonder if ruthpozuelo might be willing to comment as an MVP?
Microsoft Employee
What is your expected result? With your current DAX formula, the total of MeasureMin is -40 * 3, and total of MeasureMax is 90 * 3.
Best Regards,
Herbert
My expected results is 210 for MeasureMax (70 * 3) and 45 for MeasureMin (15 * 3).
Based on the documentation of the SUMX(<table>,<expression>) function you provide the <table> for which the <expression> is evaluated against. In the formula I expressed the <table> as a filtered context of the current filter context. It should be taking a subset of 'Table1' or the whole of 'Table1' if there are no page or selection filters being applied in the view. It then takes the resulting filtered 'Table1' and further filters it to only return rows where the Category is "c". So the resulting <table> should only be a rowset at most of rows [4, 7, 10].
The expression should then be evaluated against the values of that subset and in this case I chose to calculate the sum of the Max value, if however you dont evaluate a function and take just the [value] you get the expected total of 125.
MeasureSum = SUMX( filter(Table1, Table1[Category] = "c") , [Value] )
The other thing to point out is that if you look at the values in the other rows in the table the formula did not evaluate to anything, so then why is it suddenly ignoring the category filter?
What is really interesting is that when you remove the [Value] field it reduces the table to only the rows with a Category = "c" but still calculates total based on the Max/Min of the whole table ... my brain just cannot make sense of it.
- v-haibl-msft
To get the expected result, you need to make some changes to the second argument as below.
MeasureMax = SUMX ( FILTER ( Table1, Table1[Category] = "c" ), CALCULATE ( MAX ( Table1[Value] ), FILTER ( Table1, Table1[Category] = "c" ) ) )MeasureMin = SUMX ( FILTER ( Table1, Table1[Category] = "c" ), CALCULATE ( MIN ( Table1[Value] ), FILTER ( Table1, Table1[Category] = "c" ) ) )We can also use other formulas like below.
Measure1 = CALCULATE ( MIN ( Table1[Value] ), FILTER ( Table1, Table1[Category] = "c" ) ) * CALCULATE ( COUNTROWS ( Table1 ), FILTER ( Table1, Table1[Category] = "c" ) )Measure2 = CALCULATE ( MAX ( Table1[Value] ), FILTER ( Table1, Table1[Category] = "c" ) ) * CALCULATE ( COUNTROWS ( Table1 ), FILTER ( Table1, Table1[Category] = "c" ) )Best Regards,
HerbertHey Herbet, thank you for your feedback. I appreciate it as it helps me better understand how the DAX language is functioning under the hood.
While I cannot dispute your solution provides the required results I still have concerns. While I cannot dispute your solution provides the required results I still have concerns.
The programmer in me screams loudly at the repetative nature trying to force the language to do what I thought it should be doing in the first place. To me it feels like a calculator that does not correctly multiple two numbers so the solution is to just solve it using addition (imagine calculating 4027 * 1024 in such a way).
I was under the impression that the SUMX(<table>, <expression>) function would evaluate the <expression> using the provided <table> much the same way that the CALCULATE(<expression>,<filter1>,<filter2>…) function does.
Knowing this now I could reduce the context going into SUMX() and the only way to do that would be to leverage CALCULATE(). The reason I would prefer this method is when you get into complicated filters in real world examples that start to create very complex formulas.
MeasureMax = CALCULATE( SUMX( Table1 , MAX([Value]) ) , FILTER(Table1, Table1[Category] = "c") )
MeasureMin = CALCULATE( SUMX( Table1 , MIN([Value]) ) , FILTER(Table1, Table1[Category] = "c") )
Maybe I have misunderstood how the <expression> is being handled in terms of column based functions, I wonder if ruthpozuelo might be willing to comment as an MVP?