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

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

tomislav_mi's avatar
tomislav_mi
Helper II
4 years ago

amitchandak 

Exactly what I was struggling to get! Great approach.
Thank you!