Pivot columns to rows in dax

[bharukc]

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. 

[tamerj1 · Accepted solution]

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]

@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

[bharukc]

@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