DAX to transform table from Row into Column

[ardianfirman06]

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 

ID	Type
1	A
1	B
1	C
2	A
2	C
2	D
2	E

Tobe 

Filter Type E and transform the table as below : 

ID	A	B	C	D
1	Yes	Yes	Yes	No
2	Yes	No	Yes	Yes

Appreciate much for your help. 

[v-binbinyu-msft · Accepted solution]

Hi @ardianfirman06 ,

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.

[ardianfirman06]

thank you so much, this is solving my problem

[Ashish_Mathur]

Hi,

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

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

Hope this helps.

[ardianfirman06]

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]

Hi,

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