Forum Discussion

tejapowerbi123's avatar
tejapowerbi123
Helper V
2 years ago
Solved

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 P...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    2 years ago

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

     

  • Daniel29195's avatar
    Daniel29195
    2 years ago

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

Daniel29195's avatar
Daniel29195
Community Champion
2 years ago

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