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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch 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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.