Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

DAX to transform table from Row into Column

Hi All,

 

I would like to summarize a table using DAX. is there any way to do it ? 

below are the scenario

 

From : Source of the table 

 

IDType
1A
1B
1C
2A
2C
2D
2E

 

Tobe 

 

Filter Type E and transform the table as below : 

 

IDABCD
1YesYesYesNo
2YesNoYesYes

 

Appreciate much for your help. 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

    Please try to create a table with below dax formula:

    Table 2 =
SUMMARIZE (
    'Table',
    'Table'[ID],
    "A", IF ( CONTAINSSTRING ( CONCATENATEX ( 'Table', [Type] ), "A" ), "Yes", "No" ),
    "B", IF ( CONTAINSSTRING ( CONCATENATEX ( 'Table', [Type] ), "B" ), "Yes", "No" ),
    "C", IF ( CONTAINSSTRING ( CONCATENATEX ( 'Table', [Type] ), "C" ), "Yes", "No" ),
    "D", IF ( CONTAINSSTRING ( CONCATENATEX ( 'Table', [Type] ), "D" ), "Yes", "No" )
)

    Please refer the attached .pbix file.

     

    Best regards,
    Community Support Team_Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

    Please try to create a table with below dax formula:

    Table 2 =
SUMMARIZE (
    'Table',
    'Table'[ID],
    "A", IF ( CONTAINSSTRING ( CONCATENATEX ( 'Table', [Type] ), "A" ), "Yes", "No" ),
    "B", IF ( CONTAINSSTRING ( CONCATENATEX ( 'Table', [Type] ), "B" ), "Yes", "No" ),
    "C", IF ( CONTAINSSTRING ( CONCATENATEX ( 'Table', [Type] ), "C" ), "Yes", "No" ),
    "D", IF ( CONTAINSSTRING ( CONCATENATEX ( 'Table', [Type] ), "D" ), "Yes", "No" )
)

    Please refer the attached .pbix file.

     

    Best regards,
    Community Support Team_Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      thank you so much, this is solving my problem

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    Hi,

    Drag ID to Rows and Type to Columns.  Write this measure

    Measure = if(countrows(Data)>0,"Yes","No")

    Hope this helps.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi thank you for your suggestions. 

      is there any possibility to do it on Summarize function ? 
      I would like to used this transformation table for mapping to other table, since this table is not unique than I cannot make relationship to this table. 

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        3 years ago

        Hi,

        If that is your objective, then create a Pivot in the Query Editor.