Forum Discussion
Group by (Two Columns and Rows) based on Conditions
Hello Community,
I am trying to achieve the result below,
Requirements
1.First I want to find same order number
- 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 |
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"))hellotejapowerbi123
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 resultlet 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 🫡👍
4 Replies
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"))hellotejapowerbi123
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 resultlet 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 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.you are welcome
