New calculated column with If condition between two fields

[Anonymous]

Hi Power BI user,

I'm facing a problem with a calculated column with the following criteria:

I have two fields, "Code" and "Allocation", a code have an allocation that can be 1 or 2, sometimes a code can be 1 and 2. I would like to create a new calculated column that write 1_2 if a code is 1 and 2. I think that the only way is to create a if + lookup condition but I'm not sure how to define it.

Any suggestions?

Thank you in advance.

[Greg_Deckler · Accepted solution]

Perhaps:

Calculated Column = 
  VAR __Table = FILTER('Table',[Code] = EARLIER([Code]))
RETURN
  CONCATENATEX(__Table,[Allocation],"_")

[Anonymous]

Greg thank you very much for the support, however i'm facing with a new problem. The dax formule work fine but i noticed that sometimes the codes are repeated more and more times, so I have also concatenated the other  allocation, like 1_1, or 3_3_3 . I would only recognize the code with id 1 and 2 and keep them just one time. Any suggestion?

[Anonymous]

Thank you very much for the solution, it works perfect!

[dobregon]

try something like this

New Column = 
Var a = CONCATENATEX (
    SUMMARIZE (
        FILTER ( Table, Table[Code] = Table[Code]  ),
        Table[Allocation] 
    ),
    Table[Allocation]  ,
    "_"
)
return
if(ISBLANK(a),"Not defined", a)