cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

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
Highlighted
Super User I
Super User I

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



If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog


Highlighted
Microsoft
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

Highlighted
Helper II
Helper II

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.

 

Highlighted
Super User II
Super User II

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors