cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ShrikantKhanna Regular Visitor
Regular Visitor

calculate COUNTIF using column names in filter

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?

4 REPLIES 4
ibarrau Senior Member
Senior Member

Re: calculate COUNTIF using column names in filter

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

Microsoft v-ljerr-msft
Microsoft

Re: calculate COUNTIF using column names in filter

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

 

Regards

ShrikantKhanna Regular Visitor
Regular Visitor

Re: calculate COUNTIF using column names in filter

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.

 

Super User
Super User

Re: calculate COUNTIF using column names in filter

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

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,790)