Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

New calculated column with If condition between two fields

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.

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    Perhaps:

     

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

4 Replies

  • dobregon's avatar
    dobregon
    Impactful Individual
    6 years ago

    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)
  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    Perhaps:

     

    Calculated Column = 
  VAR __Table = FILTER('Table',[Code] = EARLIER([Code]))
RETURN
  CONCATENATEX(__Table,[Allocation],"_")
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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?