topic Pivot columns to rows in dax in DAX Commands and Tips

Pivot columns to rows in dax

bharukc — Wed, 27 Apr 2022 20:16:40 GMT

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.

Id	2022	2021	2020	2019	2018	2017
1	Yes	Yes	Yes	No	No	No
2	Yes	Yes	No	No	No	No
3	Yes	No	No	No	No	No
4	No	No	Yes	Yes	Yes	No
5	Yes	Yes	No	No	No	No
6	No	No	No	Yes	Yes	Yes
7	No	No	No	No	Yes	Yes
8	No	No	No	No	Yes	Yes
9	Yes	Yes	Yes	Yes	Yes	Yes

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

Year	Count
2022	5
2021	4

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.

Re: Pivot columns to rows in dax

amitchandak — Thu, 28 Apr 2022 01:21:25 GMT

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

Re: Pivot columns to rows in dax

bharukc — Thu, 28 Apr 2022 01:29:38 GMT

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

Re: Pivot columns to rows in dax

tamerj1 — Thu, 28 Apr 2022 06:14:01 GMT

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