Forum Discussion
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 "Call_flag" column (first calculate max "created_on" and then max "Stamp" column) for each unique ID & Product Combination. This means that the total count should show the count of unique ID&Product combinations. e.g. for Product A, the count of IDs would be 4 with the split of 3 "1s" (ID = 242977761642152961, 929999783480788916, 932585828936911719) and 1 "0"( ID = 932778067030379386".
if the latest (first calculate max "created_on" and then max "Stamp" column) ID product combination has a value of 1 for "Call_flag" then, consider 1 and similarly 0 if the latest value is 0
| PRODUCT_NAME | ORGANIZATION_NAME | ID | CHANGED_ON | HAS_CALLS | IS_TREATING | CALL_FLAG | STAMP |
| Product A | A | 242977761642152961 | 2020/04/20 | 0 | 1 | 1 | 2020/04/21 |
| Product A | A | 242977761642152961 | 2020/04/20 | 0 | 1 | 0 | 2020/04/20 |
| Product A | A | 242977761642152961 | 2020/03/20 | 0 | 0 | 1 | 2020/04/22 |
| Product A | A | 242977761642152961 | 2020/03/20 | 0 | 0 | 0 | 2020/03/20 |
| Product A | B | 929999783480788916 | 2021/05/03 | 1 | 1 | 1 | 2021/05/04 |
| Product A | B | 929999783480788916 | 2021/05/03 | 1 | 1 | 1 | 2021/05/03 |
| Product A | B | 929999783480788916 | 2021/04/03 | 0 | 0 | 1 | 2021/04/04 |
| Product A | B | 929999783480788916 | 2021/04/03 | 0 | 0 | 1 | 2021/04/03 |
| Product A | D | 932585828936911719 | 2023/09/02 | 0 | 1 | 1 | 2023/09/03 |
| Product A | D | 932585828936911719 | 2023/09/02 | 0 | 1 | 1 | 2023/09/02 |
| Product A | D | 932585828936911719 | 2023/02/02 | 1 | 1 | 1 | 2023/02/03 |
| Product A | D | 932585828936911719 | 2023/02/02 | 1 | 1 | 1 | 2023/02/02 |
| Product A | C | 932778067030379386 | 2022/07/16 | 0 | 1 | 0 | 2022/07/17 |
| Product A | C | 932778067030379386 | 2022/07/16 | 0 | 1 | 0 | 2022/07/16 |
| Product A | C | 932778067030379386 | 2022/04/16 | 1 | 0 | 0 | 2022/04/17 |
| Product A | C | 932778067030379386 | 2022/04/16 | 1 | 0 | 0 | 2022/04/16 |
| Product B | A | 242977761642152961 | 2020/04/21 | 0 | 0 | 1 | 2020/04/22 |
| Product B | A | 242977761642152961 | 2020/04/21 | 0 | 0 | 1 | 2020/04/21 |
| Product B | A | 242977761642152961 | 2020/03/02 | 0 | 0 | 1 | 2020/03/03 |
| Product B | A | 242977761642152961 | 2020/03/02 | 0 | 0 | 1 | 2020/03/02 |
| Product B | B | 929999783480788916 | 2021/05/31 | 1 | 1 | 0 | 2021/06/01 |
| Product B | B | 929999783480788916 | 2021/05/31 | 1 | 1 | 0 | 2021/05/31 |
| Product B | B | 929999783480788916 | 2021/04/08 | 1 | 1 | 0 | 2021/04/09 |
| Product B | B | 929999783480788916 | 2021/04/08 | 1 | 1 | 0 | 2021/04/08 |
| Product B | D | 932585828936911719 | 2023/09/10 | 0 | 0 | 0 | 2023/09/11 |
| Product B | D | 932585828936911719 | 2023/09/10 | 0 | 0 | 0 | 2023/09/10 |
| Product B | D | 932585828936911719 | 2023/02/22 | 1 | 1 | 0 | 2023/02/23 |
| Product B | D | 932585828936911719 | 2023/02/22 | 1 | 1 | 0 | 2023/02/22 |
| Product B | C | 932778067030379386 | 2022/07/10 | 0 | 0 | 1 | 2022/07/11 |
| Product B | C | 932778067030379386 | 2022/07/10 | 0 | 0 | 1 | 2022/07/10 |
| Product B | C | 932778067030379386 | 2022/04/14 | 0 | 1 | 1 | 2022/04/15 |
| Product B | C | 932778067030379386 | 2022/04/14 | 0 | 1 | 1 | 2022/04/14 |
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
3 Replies
itsmeanuj , if you need a column you can use earlier
maxx(filter(Table, Table[ID] = earlier(Table[ID])) , Table[Date])
refer
Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8
Or you can consider the following for measures
Latest
https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0amitchandak - Thanks for your response. But in my scenario, we first need to take latest "changed_on" date and then latest "Stamp" date in that "Changed_on" date. Also this needs to be dynamic as there is a "changed_on" date slicer at the top of the page.
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
