Max Value based upon two columns with distant filter

[CScottP]

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)  

[quantumudit · Accepted solution]

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

[CScottP]

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!

[quantumudit]

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

[CScottP]

Sir, 

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

Scott