Forum Discussion

VijayAntonyM's avatar
VijayAntonyM
Icon for Helper I rankHelper I
1 year ago
Solved

DAX Measure: identify unique rows before sum is calculated

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.

 

datekeypayerpayer_namepayer_parentpayer_parent_nameegn_codeegn_nameVolumeVolumeBusiness_UnitAttributeValue
3/1/2024013730L3HARRIS IMS-KEO0007366010L3HARRIS TECHNOLOGIES1042078000L3 TECHNOLOGIES2222Dom SPAir0
3/1/2024013730L3HARRIS IMS-KEO0007366010L3HARRIS TECHNOLOGIES1042078000L3 TECHNOLOGIES220Dom SPGround0.066276617
3/1/2024013730L3HARRIS IMS-KEO0007366010L3HARRIS TECHNOLOGIES1042078000L3 TECHNOLOGIES220Dom SPFacilities0.004344208
2/1/2024039927L-3 MARITIME SYSTEMS0007366010L3HARRIS TECHNOLOGIES1042078000L3 TECHNOLOGIES1010Dom SPAir0
2/1/2024039927L-3 MARITIME SYSTEMS0007366010L3HARRIS TECHNOLOGIES1042078000L3 TECHNOLOGIES100Dom SPGround0.036742053
2/1/2024039927L-3 MARITIME SYSTEMS0007366010L3HARRIS TECHNOLOGIES1042078000L3 TECHNOLOGIES100Dom SPFacilities0.00285356

 

 

Thanks

M. Vijay Antony

dax need help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

2 Replies

  • Rupak_bi's avatar
    Rupak_bi
    Icon for Super User rankSuper User
    1 year ago

    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.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.