Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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.
Solved! Go to 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
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
@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
@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
User | Count |
---|---|
21 | |
19 | |
12 | |
9 | |
8 |
User | Count |
---|---|
30 | |
27 | |
14 | |
13 | |
10 |