Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hey there,
I want to count the total entries for all rows and columns within a table. This isn't so hard, however, the table's number of columns will change in the future, and for maintenance purposes I want this formula to be dynamically adapting to the number of columns.
TableA right now, which should return 10 occurences.
TableA A,B,C,D o, , ,o o,o,o,o o,o,o, , ,o,
And the TableA expanded in the future should return 13 occurences with the same formula.
TableA (2019+) A,B,C,D,E o, , ,o,o o,o,o,o,o o,o,o, , , ,o, ,o
Any ideas on this?
Kind regards,
Igor
Solved! Go to Solution.
Hi @Anonymous
Assume entries in your columns is "o" in text,
Please have a look at my test
For Table A (has columns: A,B,C,D)
1.Go to Home->Edit Queries, select all columns in this table,
2.Then select "Transform->Unpivot columns"
3.colse &&apply
4.create a calculated column
count = COUNT(Table1[Value])
5. after add a column"E" in your original data source, cilck "Refresh" button, then the [count] column would count the occurence for all five columns.
Best Regards
Maggie
Hi @Anonymous
Assume entries in your columns is "o" in text,
Please have a look at my test
For Table A (has columns: A,B,C,D)
1.Go to Home->Edit Queries, select all columns in this table,
2.Then select "Transform->Unpivot columns"
3.colse &&apply
4.create a calculated column
count = COUNT(Table1[Value])
5. after add a column"E" in your original data source, cilck "Refresh" button, then the [count] column would count the occurence for all five columns.
Best Regards
Maggie
Hey Maggie,
This works perfect! Thanks for the solution.
Kind regards,
Igor
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |