Forum Discussion

tomislav_mi's avatar
tomislav_mi
Helper II
4 years ago
Solved

Calculated column - creating partition based on multiple values

Hello all,

I am struggling with how to create one complex calculated column. Desired calculated column is in orange Active Channel Customer.

The idea is to use the column Active Channel Account to create a new column Active Channel Customer
- If the account is active in a specific month, then the Active Channel Account will be a channel for customer level - column Active Channel Customer
- If there are 2 types of channels then the value in the calculated column is going to be "both"

Report DateAccountCustomerActiveActive Channel AccountActive Channel Customer
6/30/2022a1AFALSE Reseller
6/30/2022a2ATRUEResellerReseller
6/30/2022a3AFALSE Reseller
6/30/2022a4ATRUEResellerReseller
7/31/2022a1AFALSE Direct
7/31/2022a2AFALSE Direct
7/31/2022a3ATRUEDirectDirect
7/31/2022a4AFALSE Direct
6/30/2022b1bTRUEDirectBoth
6/30/2022b2bTRUEResellerBoth


Any help or advice is welcome.

Thanks!

Need Help
Show and Tell
Tips and Tricks
  • amitchandak's avatar
    amitchandak
    4 years ago

    tomislav_mi , Try a new column like

     

    new column =
    var _1 = countx(filter(Table, [Customer] = earlier([Customer]) && [Report Date] = earlier([Report Date]) && [Active Channel] = "Reseller"), [Customer])
    var _2 = countx(filter(Table, [Customer] = earlier([Customer]) && [Report Date] = earlier([Report Date]) && [Active Channel] = "Direct"), [Customer])
    return
    Switch(true(),
    not(isblank(_1)) && not(isblank(_2)) , "Both",
    not(isblank(_1)),"Reseller",
    "Direct"
    )

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    tomislav_mi , Try a new column like

     

    new column =
    var _1 = countx(filter(Table, [Customer] = earlier([Customer]) && [Report Date] = earlier([Report Date]) && [Active Channel] = "Reseller"), [Customer])
    var _2 = countx(filter(Table, [Customer] = earlier([Customer]) && [Report Date] = earlier([Report Date]) && [Active Channel] = "Direct"), [Customer])
    return
    Switch(true(),
    not(isblank(_1)) && not(isblank(_2)) , "Both",
    not(isblank(_1)),"Reseller",
    "Direct"
    )