topic Re: DAX Measure: identify unique rows before sum is calculated in DAX Commands and Tips

DAX Measure: identify unique rows before sum is calculated

VijayAntonyM — Fri, 22 Nov 2024 10:41:26 GMT

Hi Team,

Good day,

We wanted to SUM the volumn column but consider only unique records,

Highlighted expected column in YELLOW color , Would like to set 0 for duplicate records on based

datekey, payer,payer_parent,eng_code as below, Same time this should be work even for filters and slicers.

datekey	payer	payer_name	payer_parent	payer_parent_name	egn_code	egn_name	Volume	Volume	Business_Unit	Attribute	Value
3/1/2024	013730	L3HARRIS IMS-KEO	0007366010	L3HARRIS TECHNOLOGIES	1042078000	L3 TECHNOLOGIES	22	22	Dom SP	Air	0
3/1/2024	013730	L3HARRIS IMS-KEO	0007366010	L3HARRIS TECHNOLOGIES	1042078000	L3 TECHNOLOGIES	22	0	Dom SP	Ground	0.066276617
3/1/2024	013730	L3HARRIS IMS-KEO	0007366010	L3HARRIS TECHNOLOGIES	1042078000	L3 TECHNOLOGIES	22	0	Dom SP	Facilities	0.004344208
2/1/2024	039927	L-3 MARITIME SYSTEMS	0007366010	L3HARRIS TECHNOLOGIES	1042078000	L3 TECHNOLOGIES	10	10	Dom SP	Air	0
2/1/2024	039927	L-3 MARITIME SYSTEMS	0007366010	L3HARRIS TECHNOLOGIES	1042078000	L3 TECHNOLOGIES	10	0	Dom SP	Ground	0.036742053
2/1/2024	039927	L-3 MARITIME SYSTEMS	0007366010	L3HARRIS TECHNOLOGIES	1042078000	L3 TECHNOLOGIES	10	0	Dom SP	Facilities	0.00285356

Thanks

M. Vijay Antony

Re: DAX Measure: identify unique rows before sum is calculated

Rupak_bi — Sat, 23 Nov 2024 09:38:55 GMT

Hi VijayAntonyM ,

Please see below image. Hope this solves your problem using a simgle measure. I dont think making duplicate entries Zero is really required as far as your output is concerned.

Re: DAX Measure: identify unique rows before sum is calculated

Anonymous — Mon, 25 Nov 2024 08:09:12 GMT

Hi VijayAntonyM

Thank you very much Rupak_bi for your prompt reply.

First, you can create a calculated column to combine datekey, payer, payer_parent, and egn_code to form a unique identifier.

UniqueID = [datekey] & "-" & [payer] & "-" & [payer_parent] & "-" & [egn_code]

Then create a measure.

AdjustedVolume = var _rank = RANKX( FILTER( ALL('Table'), 'Table'[UniqueID] = MAX('Table'[UniqueID]) ), CALCULATE(MAX('Table'[Attribute])), , ASC, Dense ) RETURN IF( _rank = 1, SUM('Table'[Volume]), 0 )

If you don't want to create calculated columns, try that too:

Measure AdjustedVolume = var _rank = RANKX( FILTER( ALL('Table'), 'Table'[datekey] = MAX('Table'[datekey]) && 'Table'[payer] = MAX('Table'[payer]) && 'Table'[payer_parent] = MAX('Table'[payer_parent]) && 'Table'[egn_code] = MAX('Table'[egn_code]) ), CALCULATE(MAX('Table'[Attribute])), , ASC, Dense ) RETURN IF( _rank = 1, SUM('Table'[Volume]), 0 )

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.