Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
ardianfirman06
Frequent Visitor

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. 

1 ACCEPTED SOLUTION
v-binbinyu-msft
Community Support
Community Support

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" )
)

vbinbinyumsft_0-1689326428013.png

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.

View solution in original post

5 REPLIES 5
v-binbinyu-msft
Community Support
Community Support

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" )
)

vbinbinyumsft_0-1689326428013.png

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.

thank you so much, this is solving my problem

Ashish_Mathur
Super User
Super User

Hi,

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

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

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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. 

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.