How to COUNT in Power BI // COUNT, COUNTA, DISTINCTCOUNT, COUNTBLANK, COUNTROWS, COUNTX, COUNTAX
ฝัง
- เผยแพร่เมื่อ 28 มิ.ย. 2024
- 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 - fernan@solutionsabroad.co.uk
🤵 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 TH-cam.
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
![[HIGHLIGHT] หมีLIVEปะ? | EP.171 เห็นแต่ในจอ วันนี้มาต้องขอคารวะ](http://i.ytimg.com/vi/gPWLxa0e_Z8/mqdefault.jpg)
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.
Glad it helped! Thanks for watching and the for the sub!
Excellent layered 10 minute video, being able to see the table and the counted numbers on the one page seriously good idea. Well done!
Glad you liked it, thanks for watching!
Amazingly explained for the refresher I needed, thanks for all the great content!
Such a good video, slowly starting to become a pro on power bi. THANK YOU SO MUCH!
Perfect and practical explanation about the count function !!! Thank you brother
Thank you for the Countx Filter measure! Hero
This is awesome! Your videos helps me a lot!
Awesome explanation. You cant get better than this on this topic.
Perfect explanation, worth to watch for every sec.
Great explanation...it is much clear to me how to use these functions. Thanks.
Glad it was helpful!
very clearly explained. Thank you!
Fantastic video. Thank you!
Glad you liked it!
thanks for the video! super helpful
This is really helpful. Thanks
Great stuff, thanks for checking it out!
Worked like a charm!
Fantastic great to hear!
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.
Thanks for the video. It is very helpful
You are welcome
you explain really well
thanks for watching!
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!
Amazing explanation 😁
Good one, thanks
Great video!
Thanks for watching!
Great example!
Thank you!
Thank you very much
You're welcome!
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.
Excelent video sr!
thank so much
Very useful thanks
You're welcome, thanks for watching!
Good stuff! Thx!
Glad you liked it!
just perfect tks sir
Thanks Broooo , loveit
Great video! I have a question. Why if we want to count everything in the row except a specific value?
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
th-cam.com/video/Sr3OdE-jx_8/w-d-xo.html
Hope it helps!
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.
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.
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?
Nice 👍🏽, new subscriber
Great!
Just saved me >1 Hour
Thanks
You're very welcome!
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,
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?
Hi Shikha, the count should work as long as the values are non blank
perfect man
Thanks!
count function calculates blank cells as well for me.
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
Why can't we apply the distinct value count to the card visual when I create as measure
THANKSSSSSS
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.
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. ..
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.
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.
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?
Hi Phillip, measures and calc columns are identical but not similar. Here's a video explaining how they're different: th-cam.com/video/ns4VbpRKAFs/w-d-xo.html
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: th-cam.com/video/OOs-VWf20E8/w-d-xo.html
Great Video! One question, what if I want to count the number of occurence of a specific Order ID?
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
@@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
@@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!
@@SolutionsAbroad Thanks!
@@kayeiaramariedeocampo7980 you're welcome, good luck!
Great Video! I have a question. How do you put your raw data table here.
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!
awesom
Thank you!
What are the most common DAX functions used in power bi in real time.. Can u cover a video in this specific topic..
Hey there, thanks for watching! I covered my top 5 DAX functions here: th-cam.com/video/qF9eKJxT5Bg/w-d-xo.html
How are you displaying the data table here? ... Are you just using the table visualisation?
How can we select top 3 products or countries based on count (product and countries separate columns)?
Hey there, I covered that topic in this video: th-cam.com/video/vpGAf6Nkt1Q/w-d-xo.html
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
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!
How can we count distinct values with expression filters like you did with the countx
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
@@SolutionsAbroad thank you so much, it worked perfectly
How to count the number of times a text value appears in a column?
You might need to do a countif alternative in DAX to do this: www.goodly.co.in/countif-power-bi-dax/
hi i am using distinct count in excel ,how to exclude blank cells from pivot table.because blank cells value here showing as 1.
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?
How to display the row count to each record
also DITINCTCOUNTNOBLANK - To avoid the blank one (:
I am confused,I see count is taking blank value for me. Is that count takes blank value as well.
Hi great question! Blank values are skipped docs.microsoft.com/en-us/dax/count-function-dax
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?
Please I want to write a measure that counts the most occurring number in a column
I have 37 bathrooms…How can I calculate the percentage of bathrooms that are cleaned twice a day at each location?
Please make a video connecting Power Bi to Mongodb
can u xplain any project
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.
Somehow the COUNT also counts the ZEROs in a row? Why is that the case?
Please share dataset
The letters are not clear , pls zoom the formula bar .
why y cant make it in zoom it is tiny
so countx and countax are like calculate with count, interesting
Something like that, they are iterators and basically allows you to add extra filter context, similar to calculate!
I learned to count in kindergarten.
Dude! Speak way too fast!
Thanks, Fernan for your videos. Very helpful to me. How do I connect to you on Linkedln