How to COUNT in Power BI // COUNT, COUNTA, DISTINCTCOUNT, COUNTBLANK, COUNTROWS, COUNTX, COUNTAX

In this video I go through the SEVEN different ways you can count using DAX in Power BI.
Counting is probably one of the most common functions you would've been using if you work a lot of with data. Did you know that you can do more with your COUNTS in Power BI?
Here's a TLDR version of the video if you don't want to watch the whole thing:
- COUNT - Count Non-Blank Values
- COUNTA - Count Non-Blank Values, accepts TRUE/FALSE types
- DISTINCTCOUNT - Count unique values
- COUNTBLANK - Counts blank rows
- COUNTROWS - Counts rows in table
- COUNTX - Count rows with expression (like Filters)
- COUNTAX - Count rows with expression, accepts TRUE/FALSE types
-
Get started with Power BI Desktop FREE
powerbi.microsoft.com/en-us/d...
Generate mock data with Mockaroo
www.mockaroo.com/
SUM and SUMX in Power BI
• How to use SUM and SUM...
AVERAGE and AVERAGEX in Power BI
• How to use AVERAGE, AV...
-
🚩 Support us to get perks such as demo files, early access and more!
/ solutionsabroad
MY FAVOURITE POWER BI BOOKS
📕 The Definitive Guide to DAX - amzn.to/3ruYs2f
📗 DAX Patterns - amzn.to/3oVgOYs
MY GEAR
🎥 Canon 4000D DSLR Camera - amzn.to/3tCqDOw
💻 Cyberpower Wyvern PC - amzn.to/3q04QOw
🪑 Infamous Gaming Chair - amzn.to/3q3iUXw
💡 LED Studio Lighting - amzn.to/3q3ftQz
🎙 Lavalier Microphone - amzn.to/3jwKutG
📺 Monitor - amzn.to/2LuHhhO
GET IN TOUCH
📺 Website - www.solutionsabroad.co.uk
📧 Email me - [e-mail]
🤵 LinkedIn - / solutionsabroad
📘 Facebook - / solutionsabroad
📸 Instagram - / solutions_abroad
🐤 Twitter - / abroadsolutions
🤖 Reddit - / fernanespejo
🌍 WHO IS SOLUTIONS ABROAD
Hi, my name is Fernan, the one-man band of Solutions Abroad. I work as a Data Scientist based in London, UK and have over 6 years of experience working in Business Intelligence. Solutions Abroad these days is dedicated to teaching the ins and outs of Power BI, through the power of KZbin.
If you want to learn more, I’ve conveniently created playlists to help you find the right topic for you.
🐎 First, watch this video about how a typical Power BI workflow looks like
• Power BI from START to...
🗺 Learn DAX to extend your Power BI calculations
• Power BI DAX Basics OLD
📆 Stay up-to-date on all the features Power BI releases monthly
• Power BI Monthly Featu...
🧪 Get certified as a Microsoft Data Analyst Associate
• Power BI DA 100 Prepar...
💡 Learn Power BI Features to improve your dashboards
• Power BI Feature Highl...
📊 WHAT IS POWER BI?
Power BI is a business analytics tool by Microsoft. It creates a working environment suited for normal users, meaning tasks such as data extraction, cleansing, analysis and storytelling; these are made easily-accessible to citizen-users, and extendable for technical users. At face value it provides visually-stunning, interactive dashboards, but it’s built on top of so much more.
🔻 Get started with Power BI Desktop for FREE
powerbi.microsoft.com/en-us/d...
#PowerBI #DataAnalytics #BusinessIntelligence

[@sixtyfiveroses2968]

Perfect explanation, just what I was looking for! I've watched many videos, but no one explains it the way you did. Thank you again! Subscribed.

[@SolutionsAbroad]

Glad it helped! Thanks for watching and the for the sub!

[@EMKworks]

Excellent layered 10 minute video, being able to see the table and the counted numbers on the one page seriously good idea. Well done!

[@SolutionsAbroad]

Glad you liked it, thanks for watching!

[@bouldy7]

Thank you for the Countx Filter measure! Hero

[@matthewshotton86]

Amazingly explained for the refresher I needed, thanks for all the great content!

[@kunjal96]

Perfect and practical explanation about the count function !!! Thank you brother

[@faizaa5476]

Such a good video, slowly starting to become a pro on power bi. THANK YOU SO MUCH!

[@mayanknema3007]

Awesome explanation. You cant get better than this on this topic.

[@jbmh9339]

This is awesome! Your videos helps me a lot!

[@ibrahimburhankonuk]

Perfect explanation, worth to watch for every sec.

[@gnavarrolema]

Great explanation...it is much clear to me how to use these functions. Thanks.

[@SolutionsAbroad]

Glad it was helpful!

[@EngineeredFoodies]

Hi I am facing issues in the distinct count. When i use column distribution in power bi query editor the value of distinct count is right. But when I tried to calculate the same value for the same column in power bi desktop the value was different. Need your support.

[@cazvon4346]

Clearly explained supported by simple visuals - makes learning less complicated. One question, why would you not use the COUNTA and COUNTAX functions all the time as they can handle numeric, text and binary strings? Is it a refresh performance issue? Thank you.

[@user-hz6gw3uk6z]

very clearly explained. Thank you!

[@yonwabangwenze2251]

This is really helpful. Thanks

[@SolutionsAbroad]

Great stuff, thanks for checking it out!

[@wesleyharper3253]

Worked like a charm!

[@SolutionsAbroad]

Fantastic great to hear!

[@VeganSmasher]

Fantastic video. Thank you!

[@SolutionsAbroad]

Glad you liked it!

[@chinmayjoshi977]

thanks for the video! super helpful

[@gbolahanl4280]

Thanks for the video. It is very helpful

[@SolutionsAbroad]

You are welcome

[@user-cf8jx9tf2y]

Would you know how to count # of rows based on conditions on calculated measures? Measures are based on 2 different tables with one related column.

[@divyaparekh6721]

you explain really well

[@SolutionsAbroad]

thanks for watching!

[@letthemknowuncle5399]

Amazing explanation 😁

[@shikhaofficial29]

Great video! What if both the columns I am evaluating in the expression “COUNTAX(FILTER(People, People[business_unit] > 1),People[IsManager])” are of text data types?

[@SolutionsAbroad]

Hi Shikha, the count should work as long as the values are non blank

[@SivaPonakala]

Can we show count of null values against each column from a table in a single visualization .. I do not want to use countblank for each column individually.. please suggest

[@danielosorio217]

Excellent explanations! I'd like to know how to use the functions, particularly using CountX and CountAX to count "text" values (non Boolean) rather than numerical. I have a table with vendors/suppliers that deliver different parts. Some of the vendors deliver some of the same parts, so two vendors may have the same Part No.. Parts per vendor are marked as delivered Early, On-time, and Late. I want to count each of these three delivery statuses per part and vendor to then calculate the percentage of each delivery status per vendor out of the Grand Total of all deliveries and the percentage based on each delivery type/status. For now, this is the formula I came up with to count early deliveries: _CountX = COUNTAX(FILTER(Delivery, Delivery[Delivery Type] = Early()), Delivery[Delivery Type]) Thanks for your help!

[@kennethstephani692]

Great video!

[@SolutionsAbroad]

Thanks for watching!

[@aleksanderstrommer7677]

Great example!

[@SolutionsAbroad]

Thank you!

[@Pasar2490]

Great video! I have a question. Why if we want to count everything in the row except a specific value?

[@SolutionsAbroad]

Hi Sara, if you want to count everything except a specific value, you'll need to use the CALCULATE function to apply a filter context to your count. From memory, I covered this in a previous video, check it out if you haven't yet kzbin.info/www/bejne/iaOWgJd7Ys-rlZo Hope it helps!

[@shyjumathew07]

Good one, thanks

[@jevieacop]

Hey Fernan! Thanks for your videos, I've been following you for quite a while now and it helps me a lot in my work related projects. Are you a Filipino?

[@anino68]

Great explanation.... Maybe you can help me with a matter, I need to count the codes of the clients that are repeated during different years. For example, customer 373 checked in 2020, 2021, 2022 = 3 unique times. Thanks for your help.

[@tuncohighway7922]

Excelent video sr!

[@philipwatuma7120]

Very useful thanks

[@SolutionsAbroad]

You're welcome, thanks for watching!

[@zhengsiyuan5695]

Great Video! I have a question. How do you put your raw data table here.

[@SolutionsAbroad]

Hi Zheng, great question! You need to use the "Get Data" option in the ribbon to import your data into Power BI. Hope that helps!

[@1985mercedesbenz]

thank so much

[@great_filter]

Thank you very much

[@SolutionsAbroad]

You're welcome!

[@thermant8452]

Good stuff! Thx!

[@SolutionsAbroad]

Glad you liked it!

[@natynt227]

just perfect tks sir

[@SivaPonakala]

how can I show number of null values in each column from a table at a time in a table or matrix visual ? is there a way, Please suggest. ..

[@MoonShine-bs6cl]

Hi your teaching is great but how can i avail the pbix demo files which u have used in the videos for practice, please help.

[@PhillipLearnTeach]

How do you make a measure or a calculated column that consist of a measure and certain values from a columns from either same table of separate table?

[@SolutionsAbroad]

Hi Phillip, measures and calc columns are identical but not similar. Here's a video explaining how they're different: kzbin.info/www/bejne/pKSXh5Wmh7B0fNU Measures can reference columns or measures anywhere in the dataset Calculated columns is data within a table, if you want to reference columns from other tables, there has to be a relationship that connects those tables together, here's a video on relationships if it helps: kzbin.info/www/bejne/hYDWXomNm5dje5o

[@ToniaLNewton]

How does one count how many time a certain value is in that column. As in how many times 10 is listed. I can't get it to work for NPS.

[@BSITVinluanEner]

Thanks Broooo , loveit

[@DiCh-jp8ms]

Hi i can`t fiure out a problem with counting. I have a table with 5 names of people that are repeated multiple times in the table and next to this column i have another one with a status "Yes" or a blank cell. I want to count only the times that a name apears with a status yes. For example i have John repeated 5 times, 3 of which are with status yes and David repeated 3 times and 2 times are with statu yes. I want to make a table in which to show the names and how many times they have status Yes.

[@alejandropatino9095]

Great!

[@kayeiaramariedeocampo7980]

Great Video! One question, what if I want to count the number of occurence of a specific Order ID?

[@SolutionsAbroad]

Hi Kaye, great question! You might want to look at using "Group By" on your Order ID, this will give you a count of occurrences for each ID. Alternatively, you can also created a calculated column, maybe this will help: community.powerbi.com/t5/Developer/How-to-count-the-occurrences-of-each-value-in-a-column-including/td-p/151328

[@kayeiaramariedeocampo7980]

@@SolutionsAbroad Hi I tried but it keeps removing other columns. How can I retain the existing columns (that is part of the data set, but not a reference for the thing I'm counting) thereafter? For example, I tried creating a counter for a Record ID, after selecting the column, applying Group by, it does give me the column for counter, but deletes automatically the rest of my data set. Can't figure it out. huhu

[@SolutionsAbroad]

@@kayeiaramariedeocampo7980 Hey Kaye, a quick, non-complicated solution is to just create a duplicate query of this one before the GROUP BY, then merge it back so you can get your other columns. The Group By has to exclude the other columns so it can be grouped by ID, hope it helps!

[@kayeiaramariedeocampo7980]

@@SolutionsAbroad Thanks!

[@SolutionsAbroad]

@@kayeiaramariedeocampo7980 you're welcome, good luck!

[@davidgitau8825]

MeasureName=CountRows(CalculateTable(Table, Filter1,filter2))--CalculateTable gives you an opportunity return a subset of the table, with rows that meets criteria specified in the filters. Then use countrows to get subset count,

[@abdulqadeerkamangar6279]

How can we select top 3 products or countries based on count (product and countries separate columns)?

[@SolutionsAbroad]

Hey there, I covered that topic in this video: kzbin.info/www/bejne/rKGqcplsg9CnZ7M

[@tllittle]

Nice 👍🏽, new subscriber

[@baastech7840]

perfect man

[@SolutionsAbroad]

Thanks!

[@agapheputrasusilo741]

THANKSSSSSS

[@jaybilakhia5695]

Just saved me >1 Hour Thanks

[@SolutionsAbroad]

You're very welcome!

[@DashMcunfast]

How are you displaying the data table here? ... Are you just using the table visualisation?

[@jermainecameron6209]

How can we count distinct values with expression filters like you did with the countx

[@SolutionsAbroad]

Hi Jermaine, for that you will have to use a combination of DISTINCT COUNT and FILTER, here's an article that covers that community.powerbi.com/t5/Desktop/Distinct-countx/td-p/154867

[@jermainecameron6209]

@@SolutionsAbroad thank you so much, it worked perfectly

[@user-ht9ct4ih8p]

Why can't we apply the distinct value count to the card visual when I create as measure

[@allaboutfoodzbydebd8627]

awesom

[@SolutionsAbroad]

Thank you!

[@faizanbhat2626]

count function calculates blank cells as well for me.

[@amirbitan]

Great video. I have this problem: how to create a measure that counts how many specific values appear in one column when the values in another column is not unique column 1 column 2 a 1 b 2 a 3 a 1 b 4 a 5 b 6 the answer I expect to get is 3 not 4 Thanks

[@SolutionsAbroad]

Hi Amir, it sounds like you need to use the "Keep Duplicates" functionality in Power Query. Then from there you can either just do a count/countdistinct or do a "Remove Duplicates". Hope that helps!

[@jackjohnshei8374]

hi i am using distinct count in excel ,how to exclude blank cells from pivot table.because blank cells value here showing as 1.

[@SolutionsAbroad]

Hi Shane, I gotta be honest I'm not too familiar with Power Pivot. Can you not add an implicit filter to exclude blanks or N/A?

[@nehatalwar7567]

I am confused,I see count is taking blank value for me. Is that count takes blank value as well.

[@SolutionsAbroad]

Hi great question! Blank values are skipped docs.microsoft.com/en-us/dax/count-function-dax

[@giridharnaidu3662]

What are the most common DAX functions used in power bi in real time.. Can u cover a video in this specific topic..

[@SolutionsAbroad]

Hey there, thanks for watching! I covered my top 5 DAX functions here: kzbin.info/www/bejne/p3ecln6ArbloeMk

[@sathyanague3329]

Hi, I had one doubt in Power BI. I have made one measure (consider Measure 1) on distinct count for respective column which is fine. Similarly I need to pick another column based on this measure 1 which has both values and numerics. How could be done, Sir. UNIQ ID RANGES 077GGG 1 077GGG 1 077GGG 1 078GGG null 078GGG null 079GGG 5 079GGG 5 079GGG 5 079GGG 5 079GGG 5 080GGG 1 080GGG 1 081GGG null 081GGG null 081GGG null In UNIQ ID column. I have used measure as distinct count and in card i will be getting 5 count On the RANGES column, when I use card, I need to get 3 count -----> stating like avoiding the nulls and getting 1 for 077GGG 5 for 079GGG 1 for 080GGG Could u pls help on this, Sir?

[@leslieliew5712]

I have 37 bathrooms…How can I calculate the percentage of bathrooms that are cleaned twice a day at each location?

[@vipulgoel4156]

How to count the number of times a text value appears in a column?

[@SolutionsAbroad]

You might need to do a countif alternative in DAX to do this: www.goodly.co.in/countif-power-bi-dax/

[@user-ht9ct4ih8p]

How to display the row count to each record

[@manuelboadi1343]

Please I want to write a measure that counts the most occurring number in a column

[@openuni7809]

also DITINCTCOUNTNOBLANK - To avoid the blank one (:

[@rajm5349]

can u xplain any project

[@mivanows]

Please help! I want to replicate the same result in Powerquery. Here is the example in Excel: 56 77 46 3 ->formula =COUNT(A1:C1) 33 54 2 ->formula =COUNT(A2:C2) Note that the A2 is [empty] - the count result is as expected (=2) I can't find a way to replicate it in Powerquery. Powerquery returns 3 - it is considering the empty as [NULL] and counting - the result is =3, but it should be =2.

[@MS-xg4ep]

Somehow the COUNT also counts the ZEROs in a row? Why is that the case?

[@jaitiwari241]

Please share dataset

[@almyvblog7989]

Please make a video connecting Power Bi to Mongodb

[@khalidmajeed2886]

why y cant make it in zoom it is tiny

[@arunkumarramachandran5647]

The letters are not clear , pls zoom the formula bar .

[@gintomino4136]

so countx and countax are like calculate with count, interesting

[@SolutionsAbroad]

Something like that, they are iterators and basically allows you to add extra filter context, similar to calculate!

[@Phoenixspin]

I learned to count in kindergarten.

[@jlcanada6912]

Dude! Speak way too fast!

[@umagbaalex1624]

Thanks, Fernan for your videos. Very helpful to me. How do I connect to you on Linkedln