Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DAX Assistance: How to Reference another Row with Duplicate Value

Hi, 

 

Urgent help required please if possible. I've attempted to find solution myself for hoursss, but not been able to. 

 

I have two columns in Power BI, which I will use to create a new column for my desired output (Move Type column). 

Column 1 (Type)

Column 2 (Delivery Number)

 

Desired Output (Move Type column) 

Delivery NumberTypeNew Move TypeDuplicate?
305865793LargeLarge & CasesTrue
305865793CasesLarge & CasesTrue
305865791LargeLargeFalse
305865757CasesCasesFalse
305865753LargeLarge & CasesTrue
305865753CasesLarge & CasesTrue
305865726LargeLarge & CasesTrue
305865726CasesLarge & CasesTrue
305865718LargeLarge & CasesTrue
305865718CasesLarge & CasesTrue
305865716LargeLargeFalse
305865715LargeLarge & CasesTrue
305865715CasesLarge & CasesTrue
305865676LargeLarge & CasesTrue
305865676CasesLarge & CasesTrue
305865669LargeLarge & CasesTrue
305865669CasesLarge & CasesTrue
305865647LargeLargeFalse
305865635LargeLarge & CasesTrue
305865635CasesLarge & CasesTrue
305865197LargeLargeFalse

 

IF [Delivery Number] is a duplicate, then I want to check the [Type] for all those individual duplicate Delivery Numbers

IF any of the [Type] for the duplicate Delivery Numbers is "Cases", then I want the [New Type] to return "Large & Cases" for all those Delivery Numbers. 

 

Unfortunately, I see no option to upload PBIX file

 

Thanks in advance

Matt

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

    Those are for creating new columns.

     

     

    New Move Type CC = 
CONCATENATEX (
    FILTER ( data, Data[Delivery Number] = EARLIER ( Data[Delivery Number] ) ),
    Data[Type],
    " & ",
    Data[Type], DESC
)

     

    Duplicate CC = 
COUNTROWS (
    SUMMARIZE (
        FILTER ( data, Data[Delivery Number] = EARLIER ( Data[Delivery Number] ) ),
        Data[Type]
    )
) <> 1

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    This is exactly what I was looking for. Thank you so much

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    Hi,

    I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

    Those are for creating new columns.

     

     

    New Move Type CC = 
CONCATENATEX (
    FILTER ( data, Data[Delivery Number] = EARLIER ( Data[Delivery Number] ) ),
    Data[Type],
    " & ",
    Data[Type], DESC
)

     

    Duplicate CC = 
COUNTROWS (
    SUMMARIZE (
        FILTER ( data, Data[Delivery Number] = EARLIER ( Data[Delivery Number] ) ),
        Data[Type]
    )
) <> 1