Forum Discussion

ngct1112's avatar
ngct1112
Icon for Post Patron rankPost Patron
4 years ago
Solved

Union columns with changing values

Hi all,

 

I am looking for a solution(with DAX) to union a few columns in the same table(A,B,C), meanwhile, changing the values to the column name if it is "fail".

 

Orginal:

Item A B C
1PassFailPass
2FailPassFail

 

desired output: (replace the value with column name if it is fail)

ItemAppended
1Pass--> null
2Fail --> A
1Fail --> B
2Pass--> null
1Pass--> null
2Fail --> C
Need Help
  • VahidDM's avatar
    VahidDM
    4 years ago

    Hi ngct1112 

     

    Try this code to add a new table:

     

     

    Table 2 = 
union(
    SELECTCOLUMNS('Table',"Item",[Item],"Appended",if([A]="Fail","A",[A])),
    SELECTCOLUMNS('Table',"Item",[Item],"Appended",if([B]="Fail","B",[B])),
    SELECTCOLUMNS('Table',"Item",[Item],"Appended",if([C]="Fail","C",[C]))
)

     

     

    Output:

     

     

    If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

    Appreciate your Kudos!!

     

     

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    I am not sure if I understood your question correctly, but please check the below picture.

    It is for the table creation.

     

    New Table =
    UNION (
    SUMMARIZECOLUMNS (
    Data[Item],
    "Appended", IF ( MAX ( Data[A] ) = "Pass", "null", "A" )
    ),
    SUMMARIZECOLUMNS (
    Data[Item],
    "Appended", IF ( MAX ( Data[B] ) = "Pass", "null", "B" )
    ),
    SUMMARIZECOLUMNS (
    Data[Item],
    "Appended", IF ( MAX ( Data[C] ) = "Pass", "null", "C" )
    )
    )

     

  • VahidDM's avatar
    VahidDM
    Icon for Super User rankSuper User
    4 years ago

    Hi ngct1112 

     

    Try this code to add a new table:

     

     

    Table 2 = 
union(
    SELECTCOLUMNS('Table',"Item",[Item],"Appended",if([A]="Fail","A",[A])),
    SELECTCOLUMNS('Table',"Item",[Item],"Appended",if([B]="Fail","B",[B])),
    SELECTCOLUMNS('Table',"Item",[Item],"Appended",if([C]="Fail","C",[C]))
)

     

     

    Output:

     

     

    If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

    Appreciate your Kudos!!