calculate COUNTIF using column names in filter

[ShrikantKhanna]

Hi,

Need some direction here Please.

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?

[v-ljerr-msft]

Hi @ShrikantKhanna,

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). 

Regards

[ShrikantKhanna]

Thanks @v-ljerr-msft and @ibarrau .

It would have been easy if there was a way. It is very tedious to pick every column separately and calculate instances. Right to Unpivot is not present for me.

[TomMartens]

Hey,

not sure if this

https://community.powerbi.com/t5/Desktop/Countif-for-specific-customers/m-p/225708#M100454

relates exactly to what you are looking for, but maybe this gets you started.

Regards

[ibarrau]

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.

Regards, hope this helps