Forum Discussion
Solved
Max value based on dynamic date filter
I need to calculate something similar to the "partition by" function in SQL. My data contains multiple instances of IDs. I need to create a measure that would pick up the latest value of the "Ca...
Hi itsmeanuj ,
Here are the steps you can follow：
1. Create measure.
Measure = var _maxdatecreateon= MAXX( FILTER(ALLSELECTED('Table'), 'Table'[PRODUCT_NAME]=MAX('Table'[PRODUCT_NAME])&&'Table'[ID]=MAX('Table'[ID])),[CHANGED_ON]) var _maxstamp= MAXX( FILTER(ALLSELECTED('Table'), 'Table'[PRODUCT_NAME]=MAX('Table'[PRODUCT_NAME])&&'Table'[ID]=MAX('Table'[ID])&&'Table'[CHANGED_ON]=_maxdatecreateon),'Table'[STAMP]) return MAXX( FILTER(ALL('Table'), 'Table'[PRODUCT_NAME]=MAX('Table'[PRODUCT_NAME])&&'Table'[ID]=MAX('Table'[ID])&&'Table'[CHANGED_ON]=_maxdatecreateon&&'Table'[STAMP]=_maxstamp),'Table'[CALL_FLAG])count of IDs = CALCULATE( DISTINCTCOUNT( 'Table'[ID]),FILTER(ALL('Table'),'Table'[PRODUCT_NAME]=MAX('Table'[PRODUCT_NAME])))2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi itsmeanuj ,
Here are the steps you can follow：
1. Create measure.
Measure =
var _maxdatecreateon=
MAXX(
FILTER(ALLSELECTED('Table'),
'Table'[PRODUCT_NAME]=MAX('Table'[PRODUCT_NAME])&&'Table'[ID]=MAX('Table'[ID])),[CHANGED_ON])
var _maxstamp=
MAXX(
FILTER(ALLSELECTED('Table'), 'Table'[PRODUCT_NAME]=MAX('Table'[PRODUCT_NAME])&&'Table'[ID]=MAX('Table'[ID])&&'Table'[CHANGED_ON]=_maxdatecreateon),'Table'[STAMP])
return
MAXX(
FILTER(ALL('Table'), 'Table'[PRODUCT_NAME]=MAX('Table'[PRODUCT_NAME])&&'Table'[ID]=MAX('Table'[ID])&&'Table'[CHANGED_ON]=_maxdatecreateon&&'Table'[STAMP]=_maxstamp),'Table'[CALL_FLAG])count of IDs =
CALCULATE(
DISTINCTCOUNT(
'Table'[ID]),FILTER(ALL('Table'),'Table'[PRODUCT_NAME]=MAX('Table'[PRODUCT_NAME])))2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly