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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
bharukc
Helper I
Helper I

Pivot columns to rows in dax

I have a table that I need pivoted so that I can have all the years in one column and create visualization off of it. Id is the id and year columns are if the id was open during that year. 

 

Id202220212020201920182017
1YesYesYesNoNoNo
2YesYesNoNoNoNo
3YesNoNoNoNoNo
4NoNoYesYesYesNo
5YesYesNoNoNoNo
6NoNoNoYesYesYes
7NoNoNoNoYesYes
8NoNoNoNoYesYes
9YesYesYesYesYesYes

 

Untimately, I want to be able to create a column that just displays year and count of id (that have yes value)

YearCount
20225
20214

 

I can create multiple measures for each calculations to get the results but I want to have a column for each year and create count automatically when I drag ID field in the visualizations. 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @bharukc 
Yes it can be done using DAX but it shall require some hard coding and the number of coulmns must be fixed. Please follow the same steps in this post. I hope that you don't face any circular dependancy errors given that most of your columns are calculated columns
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Count-Text-in-Columns/m-p/2467106

View solution in original post

3 REPLIES 3
tamerj1
Super User
Super User

Hi @bharukc 
Yes it can be done using DAX but it shall require some hard coding and the number of coulmns must be fixed. Please follow the same steps in this post. I hope that you don't face any circular dependancy errors given that most of your columns are calculated columns
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Count-Text-in-Columns/m-p/2467106

amitchandak
Super User
Super User

@bharukc , You can unpivot all the year column in power query , that can help, and then count the number yes or no as per need  in a measure 

 

https://radacad.com/pivot-and-unpivot-with-power-bi

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

@amitchandak , all these are calculated column using two dates column. Is there a way to do it in dax. As power query editor will not display the calculated columns

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.