Group by (Two Columns and Rows) based on Conditions

[tejapowerbi123]

Hello Community,

I am trying to achieve the result below,

Requirements

1.First I want to find same order number

1. Once I have same order number, I want to check condition for Parts Column,
   If Parts=ABC and Parts=DEF and same Order Number, then “Connected”
   or
   If Parts=LMN and Parts=DEF and same Order Number, then “Not-Connected”
   or
   If Single row for Parts=DEF then Single
   ***I have tried "Earlier" function but not able to achieve the result.

Dataset

Order Number	Parts
123	ABC
123	DEF
456	LMN
456	DEF
789	DEF

Expected Result

Order Number	Parts	Category
123	ABC	Connected
123	DEF	Connected
456	LMN	Not-Connected
456	DEF	Not-Connected
789	DEF	Single

[ryan_mayu · Accepted solution]

@tejapowerbi123 

pls try this

Column = IF('Table'[Parts]="DEF",if(CALCULATE(count('Table'[Parts]),ALLEXCEPT('Table','Table'[Order Number]))=1,"Single",if(maxx(FILTER('Table','Table'[Order Number]=EARLIER('Table'[Order Number])&&'Table'[Parts]="ABC"),'Table'[Parts])<>"","Connected","Not_Connected")),if('Table'[Parts]="ABC"&&maxx(FILTER('Table','Table'[Order Number]=EARLIER('Table'[Order Number])&&'Table'[Parts]="DEF"),'Table'[Parts])<>"","Coneected","Not_Connected"))

[tejapowerbi123]

@ryan_mayu @Daniel29195 
I want to say thank you so much to both of you. I have tried both solutions, which work perfectly for my massive data.

[ryan_mayu]

you are welcome

[Daniel29195 · Accepted solution]

hello@tejapowerbi123 

caluclated column : 

Column =

var order_nb = table20[Order Number]

var combo_1 = { "ABC" , "DEF" }

var combo_2 = {"LMN", "DEF" }

var combo_3 = {"DEF"}

var datasource  =

SELECTCOLUMNS(

    FILTER(

        table20,

        table20[Order Number] = order_nb

    ),

    table20[Parts]

)

var result  =

SWITCH(

    TRUE(),

    COUNTROWS(FILTER(datasource , table20[Parts] in combo_1)) =2    , "Connected" ,

    COUNTROWS(FILTER(datasource , table20[Parts] in combo_2)) = 2 , "Not Connected" ,

    COUNTROWS(FILTER(datasource , table20[Parts] in combo_3)) = 1 , "Single"

)

return result

let me know if it works out for you . 

If my response has successfully addressed your issue kindly consider marking it as the accepted solution! This will help others find it quickly. Dont forget to hit that  thumbs up button 🫡👍

[ryan_mayu · Accepted solution]

@tejapowerbi123 

pls try this

Column = IF('Table'[Parts]="DEF",if(CALCULATE(count('Table'[Parts]),ALLEXCEPT('Table','Table'[Order Number]))=1,"Single",if(maxx(FILTER('Table','Table'[Order Number]=EARLIER('Table'[Order Number])&&'Table'[Parts]="ABC"),'Table'[Parts])<>"","Connected","Not_Connected")),if('Table'[Parts]="ABC"&&maxx(FILTER('Table','Table'[Order Number]=EARLIER('Table'[Order Number])&&'Table'[Parts]="DEF"),'Table'[Parts])<>"","Coneected","Not_Connected"))