DAX Measure: identify unique rows before sum is calculated

[VijayAntonyM]

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

[Anonymous · Accepted solution]

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.

[Rupak_bi]

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.