Forum Discussion
Max Value based upon two columns with distant filter
| TYPE | ACTION | MODIFICATION | DATE | ITEMS | SPLIT_MOD | ORDER_ACTION | MOD_ORDER | EXPECTED RESULTS | ITEMS | |
| WIDGET-A | BASE | 01-Dec-24 | 57 | 0 | 0 | 0 | WIDGET-A | 56 | ||
| WIDGET-A | ADD | BASE | 01-Dec-24 | 57 | 0 | 1 | 1 | WIDGET-B | 75 | |
| WIDGET-A | CHANGE FROM | BASE | 01-Dec-24 | 57 | 0 | 0 | 0 | WIDGET-C | 0 | |
| WIDGET-A | NO CHANGE | BASE | 01-Dec-24 | 57 | 0 | 4 | 4 | |||
| WIDGET-A | CHANGE FROM | CHANGE 03 | 01-Dec-24 | 57 | 3 | 0 | 3 | TOTAL | 131 | |
| WIDGET-A | CHANGE TO | CHANGE 03 | 01-Dec-24 | 57 | 3 | 3 | 6 | |||
| WIDGET-A | CHANGE TO | CHANGE 04 | 01-Dec-24 | 56 | 4 | 3 | 7 | |||
| WIDGET-A | NO CHANGE | CHANGE 04 | 01-Dec-24 | 56 | 4 | 4 | 8 | |||
| WIDGET-B | CHANGE FROM | BASE | 22-Dec-24 | 55 | 0 | 0 | 0 | |||
| WIDGET-B | BASE | 22-Dec-24 | 23 | 0 | 0 | 0 | ||||
| WIDGET-B | ADD | BASE | 22-Dec-24 | 55 | 0 | 1 | 1 | |||
| WIDGET-B | NO CHANGE | BASE | 22-Dec-24 | 55 | 0 | 4 | 4 | |||
| WIDGET-B | CHANGE FROM | CHANGE 03 | 22-Dec-24 | 55 | 3 | 0 | 3 | |||
| WIDGET-B | NO CHANGE | CHANGE 03 | 22-Dec-24 | 55 | 3 | 4 | 7 | |||
| WIDGET-B | CHANGE FROM | CHANGE 04 | 22-Dec-24 | 55 | 4 | 0 | 4 | |||
| WIDGET-B | CHANGE TO | CHANGE 04 | 22-Dec-24 | 100 | 4 | 3 | 7 | |||
| WIDGET-B | CHANGE TO | CHANGE 05 | 22-Dec-24 | 75 | 5 | 3 | 8 | |||
| WIDGET-B | NO CHANGE | CHANGE 05 | 22-Dec-24 | 75 | 5 | 4 | 9 | |||
| WIDGET-C | CHANGE FROM | BASE | 22-Dec-24 | 20 | 0 | 0 | 0 | |||
| WIDGET-C | ADD | BASE | 22-Dec-24 | 20 | 0 | 1 | 1 | |||
| WIDGET-C | CHANGE TO | BASE | 22-Dec-24 | 20 | 0 | 3 | 3 | |||
| WIDGET-C | DELETE | 22-Dec-24 | 20 | 0 | 5 | 5 |
Trying to filter/group by "TYPE"
Then get "ITEMS" value based upon MAX "MOD_ORDER"
If "ACTION" is "DELETE" then reflect "ITEMS" value as "0" (Zero)
Hello CScottP
Thank you for providing the dataset and the expected result table.You can create the following DAX measure to calculate number of items:
Item Count = VAR _maxModOrd = MAX ( 'Table'[MOD_ORDER] ) VAR _actionValues = CALCULATE ( MAX ( 'Table'[ACTION] ), 'Table'[MOD_ORDER] = _maxModOrd ) VAR _items = CALCULATE ( SUM ( 'Table'[ITEMS] ), 'Table'[MOD_ORDER] = _maxModOrd ) VAR _trueItems = IF ( _actionValues = "DELETE", 0, _items ) // For Total Calculation VAR _summarizedTable = SUMMARIZE ( 'Table', 'Table'[TYPE], "max_mod_order", CALCULATE ( MAX ( 'Table'[MOD_ORDER] ) ) ) VAR _extendedTotalTable = FILTER ( ADDCOLUMNS ( _summarizedTable, "action", LOOKUPVALUE ( 'Table'[ACTION], 'Table'[TYPE], [TYPE], 'Table'[MOD_ORDER], [max_mod_order] ), "items", LOOKUPVALUE ( 'Table'[ITEMS], 'Table'[TYPE], [TYPE], 'Table'[MOD_ORDER], [max_mod_order] ) ), [action] <> "DELETE" ) VAR _totalItems = SUMX ( _extendedTotalTable, [items] ) RETURN IF ( ISINSCOPE ( 'Table'[TYPE] ), _trueItems, _totalItems )Here is the screenshot of the result I got after applyint this DAX measure:
Here is the screenshot of the DAX measure:
I am also attaching the working Power BI file that I have used to solve your query, in case you need it.
Best Regards,
Udit
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍
🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTreePround to be a Super User
4 Replies
Hello CScottP
Thank you for providing the dataset and the expected result table.You can create the following DAX measure to calculate number of items:
Item Count = VAR _maxModOrd = MAX ( 'Table'[MOD_ORDER] ) VAR _actionValues = CALCULATE ( MAX ( 'Table'[ACTION] ), 'Table'[MOD_ORDER] = _maxModOrd ) VAR _items = CALCULATE ( SUM ( 'Table'[ITEMS] ), 'Table'[MOD_ORDER] = _maxModOrd ) VAR _trueItems = IF ( _actionValues = "DELETE", 0, _items ) // For Total Calculation VAR _summarizedTable = SUMMARIZE ( 'Table', 'Table'[TYPE], "max_mod_order", CALCULATE ( MAX ( 'Table'[MOD_ORDER] ) ) ) VAR _extendedTotalTable = FILTER ( ADDCOLUMNS ( _summarizedTable, "action", LOOKUPVALUE ( 'Table'[ACTION], 'Table'[TYPE], [TYPE], 'Table'[MOD_ORDER], [max_mod_order] ), "items", LOOKUPVALUE ( 'Table'[ITEMS], 'Table'[TYPE], [TYPE], 'Table'[MOD_ORDER], [max_mod_order] ) ), [action] <> "DELETE" ) VAR _totalItems = SUMX ( _extendedTotalTable, [items] ) RETURN IF ( ISINSCOPE ( 'Table'[TYPE] ), _trueItems, _totalItems )Here is the screenshot of the result I got after applyint this DAX measure:
Here is the screenshot of the DAX measure:
I am also attaching the working Power BI file that I have used to solve your query, in case you need it.
Best Regards,
Udit
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍
🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTreePround to be a Super User
Sir,
Perfect! Thank you for your time and effort, greatly apprecated!
Scott
When I apply to larger data volume I'm getting the following, I did pair it off to same column names and checked column types as well.
Mdx Script (Model) (47,37) Clulation error in measue with card & Multi card in your example.
Measure showed - The function SUM can not work with values of type string.
Thanks in advance!
Hello CScottP
I suspect the issue lies within the data model. Sharing a sample, anonymized Power BI file would be extremely helpful for me to diagnose the problem.
DAX formulas generally perform consistently regardless of data size, provided the data types and other relevant factors are identical. Since it's not working as expected, I'll need to examine the Power BI file and other details to understand why.
If you prefer not to share the anonymized Power BI file publicly, please feel free to send it to me directly through message.
Thanks,
Udit
