Forum Discussion
Solved
Groupby and return values based on another column
I want to return the Value at the max Date for each ID. (then avergage those values) I am able to return the max Value for each ID, but not the value at the max Date of each ID. Here is what I h...
You can use a variable to store the Max date, then find the value for that date:
Average_Max_Day = AVERAGEX( Values( test_table[ID]) , VAR _ID = test_table[ID] VAR _MaxDay = MAXX(FILTER(test_table, test_table[ID] = _ID), test_table[Date] ) RETURN SUM( test_table[Value] ) --change this to average or max depending on how you want to aggregate the value per day per ID )
AllisonKennedy
Community Champion
Community Champion
You can use a variable to store the Max date, then find the value for that date:
Average_Max_Day =
AVERAGEX( Values( test_table[ID]) ,
VAR _ID = test_table[ID]
VAR _MaxDay = MAXX(FILTER(test_table, test_table[ID] = _ID), test_table[Date] )
RETURN
SUM( test_table[Value] ) --change this to average or max depending on how you want to aggregate the value per day per ID
)
That did the trick. Thank you very much. How have I never realized we can write variables inside functions?
I'm still not entirely sure how this works, but I was able to play around with it to see how I can get different results.
- AllisonKennedy
klintala Awesome - so glad it worked. Variables inside functions is an advanced DAX concept and a great way to manipulate the evaluation context of the variable. You've just opened up a whole new world. 🙂