Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply

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

  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

 

 

 






2 ACCEPTED SOLUTIONS
ryan_mayu
Super User
Super User

@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"))

 

11.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Daniel29195
Super User
Super User

hello@tejapowerbi123 

 

Daniel29195_0-1706143625395.png

 

 

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

 

Daniel29195_1-1706143672285.png

 

 

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 🫡👍

View solution in original post

4 REPLIES 4

@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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Daniel29195
Super User
Super User

hello@tejapowerbi123 

 

Daniel29195_0-1706143625395.png

 

 

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

 

Daniel29195_1-1706143672285.png

 

 

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
Super User
Super User

@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"))

 

11.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.