topic Max Value based upon two columns with distant filter in DAX Commands and Tips

Max Value based upon two columns with distant filter

CScottP — Tue, 11 Feb 2025 18:52:34 GMT

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)

Re: Max Value based upon two columns with distant filter

quantumudit — Tue, 11 Feb 2025 20:11:15 GMT

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: LinkTree

Pround to be a Super User

Re: Max Value based upon two columns with distant filter

CScottP — Wed, 12 Feb 2025 12:54:33 GMT

Sir,

Perfect! Thank you for your time and effort, greatly apprecated!

Scott

Re: Max Value based upon two columns with distant filter

CScottP — Thu, 13 Feb 2025 13:15:19 GMT

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!

Re: Max Value based upon two columns with distant filter

quantumudit — Thu, 13 Feb 2025 17:59:57 GMT

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