I have a table of 20 attributes in staff data. Each attribute has a YES/NO saved covering all the items/services issued to staff. If the staff is using attribute 'A' then there is a 'Yes' saved in that column. This is done in sharepoint list to continuously hold updated information and I have to create a management report in Power BI to represent how many people are using a particular service/attribute and keep it a real time dashboard.
I tried transpose in Power query but it says it does not have adequate rights to transpose. I tried "Summarize Column" function and created a new table but that is also not helping me out.
I have to count all YES from each column and represent in a two column table where all the column names are represented in rows and count of YES next to them.
Is there a way I can use calculate function to count number of YES by creating a random option in filter function to match column name with name available in row?
And whenever there is a a new column added in sharepoint list Power BI should add that column name in the new table created and start counting number of YES in that new column?
Hi, i don't think you can do that. Think about it, you want a value counting each table column dinamically and the columns of matrix like rows of matrix but only one column of matrix showing value of multiple table columns.
Try adding for each column a label, and add a visual filter when is "Yes". This should solve it.
I tried transpose in Power query but it says it does not have adequate rights to transpose.
It could be an easy task in Query Editor with the Unpivot option. But without adequate rights to use this option, I don't think there is an alternative way to do the same thing using DAX(calculate column/table/measure).