DAX Fridays! #4: COUNT, COUNTA, COUNTX, COUNTAX, DISTINCTCOUNT AND COUNTROWS in DAX
Vložit
- čas přidán 1. 07. 2024
- Let's go through all the count expressions available in DAX!
Here you have the keynotes for the video so you can jump to the function you want to learn:
02:44 COUNT text (#ERROR)
03:24 COUNT numbers
04:20 COUNT blanks
04:56 COUNT dates
05:36 COUNTA text
06:44 COUNTA numbers
07:27 COUNTX
09:16 COUNTAX
09:58 DISTINCTCOUNT
10:59 COUNTROWS(DISTINCT)
12:06 COUNTBLANK
12:34 COUNTROWS
13:32 COUNTROWS with columns (#ERROR)
14:33 COUNTROWS with an expression
Link to Curbal Glossary: curbal.com/blog/glossary/COUN...
PREVIOUS VIDEO: • DAX Fridays! #3: SUM a...
NEXT VIDEO: • DAX Fridays! #5: CALCU...
Dowload the sample file here:
curbal.com/blog/glossary/coun...
Looking for a download file? Go to our Download Center: curbal.com/donwload-center
SUBSCRIBE to learn more about Power and Excel BI!
/ @curbalen
Our PLAYLISTS:
- Join our DAX Fridays! Series: goo.gl/FtUWUX
- Power BI dashboards for beginners: goo.gl/9YzyDP
- Power BI Tips & Tricks: goo.gl/H6kUbP
- Power Bi and Google Analytics: goo.gl/ZNsY8l
ABOUT CURBAL:
Website: www.curbal.com
Contact us: www.curbal.com/contact
QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
► Twitter: @curbalen, @ruthpozuelo
► Google +: goo.gl/rvIBDP
► Facebook: goo.gl/bME2sB
► Linkedin: goo.gl/3VW6Ky
#DAXFRIDAYS #CURBAL #DAX #POWERBI #MVP
Thank you :) This video helps makes the different count functions clearer (especially for COUNTX and COUNTAX)
Thank you, Ruth! I started to watch your videos. They are very helpful.
Great video. Thanks for clarifying. I think this will help me remember this forever now!
Excellent video! I have been confused by the many counts since I started learning DAX recently. This video is very helpful clearing it out! Thank you!
You welcome, glad it was useful:)
/Ruth
Muchas gracias! estoy aprendiendo mucho, tiene una excelente forma de transmitir sus conocimientos.
+Norberto Vera Reatiga Muchas gracias Norberto! Mi madre es profesora quizás se me haya pegado algo :)
/Ruth
Just started watching your DAX videos from #1, very helpful indeed, will soon be on track to the latest videos. Thank you @Curbal
Well done! That is a loy of videos ;)
/Ruth
Thank you and much much appreciated. Well done.
Your content is great, i love the way you talk and explain. Awesome
Thanks!
Very clear explanation and demo! Thankyou!
Amazing, so simple. Thanks
Very clear, with your videos, the DAX become easier
Soooo happy to hear that 😀
/Ruth
Thanks a lot!! Such a great series of videos. Nice content.
Yey!!
I m a bit new in Dax formulas, good video helping me in this stage.
Excellent! Thanks for the feedback :)
/Ruth
Excellent
*It's great things that you speak normal and slow English that is very easy to understand us very easily. And thank here I understand everyone's very easily*
My pleasure !
/Ruth
great video - thanks!
what a nice Video, thanks for that, greeting from Mexico.
Un placer ;)
/Ruth
You are the best!
Dax friday is amazing, I know you have like 150 videos of dax, but thanks for all this.
162 to be exact ;)
/Ruth
162 to be exact ;)
/Ruth
Hi Ruth, great video! I have a question. Im trying to calculate the value of column A in new measure. The calculation is based on sliced dates with same ID. is it possible replace null value with 0 if the ID doesn’t exist in the later date?
Thanks so much. Very useful. Can you explain how to sum a numerical columns value but only for certain words. So for the example you're using, sum stock quantity for EMEA in stock location.
Thanks for the video
Great Video. Thanks Ruth.
+NOAH Thanks Noah! I have already planned the next two videos for DAX Fridays, but after that, any requests?
/Ruth
Your videos are very helpful. Learned a lot. Thanks Ruth.
+NOAH thanks Noah! /Ruth
Oh .... Sh ... Short. Giggle. I am going to use that! Love your videos. Thanks.
😂 thanks to you for watching!
/Ruth
amazing , thanks
I love you, Ruth. You're the best. Thank you so much!
That is true Power Bi passion!! 😂😂
You welcome!
/Ruth
@@CurbalEN Hahaha! Good!
Good and helpful!
+Bhaktha Singh Excellent thanks!
/Ruth
Ruth, can you explain how did you get into 2.19? What did you do after you choose ProductName cell?
@Curbal @Ruth .. Thanks for the lovely video. Your videos have always been a one stop solution to understand any function on Power BI.
Wanted your clarifications on 2 points in this video.
1.Count works with text in Power BI. Pls do let me know if my understanding is correct.
2. In Power BI If we use CountAX(ProductData, [StockQty]>100) returns value 5 (not Correct)
However, this changes to 2 when we use a filter expression and gives 2. Think this is again to do with the changing of context? Is my understanding correct.
Regards,
Harsh
Hey, great video :) I have a question, what is the advantage of this X functions (COUNTX, SUMX, etc) over the CALCULATE function?
Count, sum while calculate the time it allows single column and it not allowed any expression filters.countx and sumx allowed expression along with filters also
Great Tutorial !!
+Tobe Ezeife Thanks Tobe! Did you know that DAX had so many count functions? Never realized it until I did this video :)
Have a great weekend!
/Ruth
I have only used the Count and DistinctCount but now i know better Thanks :D
+Tobe Ezeife Believe me I learned a few things too doing the video! ;)
/Ruth
Count = Counts both Text and Number (excluding Boolean and Blank)
CountA =Counts Text, Number and Boolean(excluding Blank)
CountRows = Counts all the rows in the table even the blank rows, here you use only table reference not expression
DistinctCOunt = gives the distinct numbers of count includes Blank and Boolean
CountBlank = Counts only blanks
BLANK is the equivalent of null, while "empty" is the equivalent of "" (an empty string).
Count doesn't count text.
Amazing Video, Ruth, just want to know that can't we count column by using DAX?
Thank you so much Ruth!.
Just noticed, COUNT works with string. May be its an update.
Hi , thanks for your video they are very helpful!
I have a tricky question i hope you can Help!
I have a calculated column"FRUITS" , that returns me 3 type of value : Apple,Pear,Banana. Its a calculation like (If this then apple, if that then pear etc)
Now, some of the row are Apple and pear but when i do count of Pear, pear wont be calculated.
Example:
Apple
Apple ,Pear
Pear
Banana,apple
What i have as count(Filtered by a visual)
Apple 2
Pear 1
Banana 1
What i am looking for:
Apple 3
Pear 2
Banana 1
How i can write the formula that assign the Value Apple,Pear,Banana in a way that when i count it i got the correct count?
The visual i'm using is "Facet Key" where in "instance" i selected my Calculated column "FRUITS" and in Count i have a measure (Which is done by Calculate(COUNTA "FRUITS" ,userleationship{filter})
How can I get the functionality of countif function from excel in power Bi?
@Ruth, first great video as usual! Second: I could not download the file. Message "It is not possible to download files". I am from Brazil. As your service is abroad, does it have some download restriction area?
Hi,
No, no restrictions, all the files have been moved to the download center here:
curbal.com/donwload-center
/Ruth
Hi Ruth, Hope you are doing well, I have one query If I wanna distinct count of the "stock location" without blanks in my count. The output will be 2 (EMEA and AMER) then do I need to use ALL Function or something else. Please clear my doubt. Thanks in Advance
Thanks Ruth!
Are you watching all videos?
I plan too. I recreate your steps in my own master pbix so I can reference it as needed. Recreating the DAX and testing it makes it stick better vs just understanding your videos. Out of all the PBI content providers, yourself and Enterprise DNA are my favorites (you are first of course), and since you're always giving away FREE content, I purchased your website training to give something back for your efforts. Ultimately, I think knowing DAX well is what really separates a Power BI Pro from a Power BI user. I want to be a Pro. After all, like you said, "The effort is high. The rewards are higher!"
Keep up the Great Work!
Thanks! :)
It sounds like you are like me: you need to do it yourself in order to learn! :) Keep pushing, it is worth the effort !!
My consulting business takes care of my bills, so for as long as I can, I will keep sharing as much as I can for free. It brings me joy to be able to do it.
Thankfully I am my boss so I can take silly decisions like that ;)
Hi Ruth, but how can I count EMEA, Without ="EMEA"? Row 1, row 3 and row 5 need to be the result 3. Thank you.
Hey, great video as usual. I wondered if you could use COUNTROWS to count within categories. For example in your table above, can you count rows of every stock location for example.
Use calculate and allexcept(category) to do things in groups. :)
/Ruth
@@CurbalEN Thanks Ruth. I resolved the issue in the query editor by using the group-by button.
That works too
/Ruth
Thumbs up!
🎉🎉
/Ruth
Merci
Thanks again.
Are you watching all the Dax Fridays videos?
/Ruth
Hi Ruth. My intention is to do about 15 -20 videos per week and include practice with some data that I have. Will also purchase a few books shortly to help.
Oh! I hope the videos/ books are helpful and you get your wings :D
Welcome to the PBi team!!
/Ruth
Thanks. I will keep you updated with any issues.
I always short of time! if you have questions on your data, the power bi community is a great place to get help :)
/Ruth
Great information Ruth..
Great! Glad it was useful :)
/Ruth
@@CurbalEN I am facing issue with the multiple countif from the same table still,I want to know how multiple countifs we can write in dax for multiple crietria .For example if i want to calculate the count of Region ,Brand and Size and the information is in text. Can you please help me with that.
Hi, the power bi community is perfect for support, post your question there!
/Ruth
@@CurbalEN Sure, Thanks a lot Ruth..
www.pbiusergroup.com/blogs/abhishek-tripathi/2018/12/16/multiple-count-if?CommunityKey=b35c8468-2fd8-4e1a-8429-322c39fe7110&tab=
Can we choose top 5 products by count of product names??
Hi, thanks for valued information.
what if i want to count cells in a row, like if i am using matrix report i want to count by row not column.
All X functions will count row by row.
/Ruth
can you please help, how can i write expression for count group by date. explaining, more than two transaction in same date should count one.
Hi! Could you post in the Power bi Community? Thanks!
/Ruth
Hi Ruth, how to distinct count in Slicer ?
@Ruth, hi...good morning....have you any videos on QA portion .." means - how to create in service level"
I have this one, but I just test the functionality in the mobile
m.czcams.com/video/ljYMcDOX8yo/video.html
/Ruth
I was working on that project with Raph...we need a dax function that can dynamically assign row numbers, starting from one with increment of one anytime slicers or filters are applied. We tried the generate function and it would not work. Please help
Hi, could you please post your question in the power bi community? Thanks!
/Ruth
Hello, great content :)
I have tried COUNT and COUNTA in Power BI desktop and couldn't find a scenario where I could spot the difference.
I use COUNT on non-numeric cells without any issues, please advise, thank you!
+Radek Ouhel ml. Hi Radek,
Does this help?
The COUNTA function counts the number of cells in a column that are not empty. It counts not just rows that contain numeric values, but also rows that contain nonblank values, including text, dates, and logical values.
/Ruth
Hi Ruth, thank you for taking time to reply.
MY issue was that the described behavior and the one I was getting was simply different.
However my data set was simply "Enter Data" sample I made by myself not Excel and that I believe made the difference.
Interesting observation I've made was that if I switched data type in Query Editor to Text (from Number), I could see it to behave as you describe. However changing data type nor formatting in the model itself has no difference whatsoever.
Thanks
+Radek Ouhel ml. Mmmm interesting behavior ! 🤔
Thanks for sharing!
/Ruth
Hi Ruth, i want to count cases closed and opened month on month. I created a calendar and dont know what to do next. I want to create a chart to graph cases opened, closed and backlog month on month
+swativish Hi swativish,
Here is an example on how to do it:
www.sqlbi.com/articles/analyzing-events-with-a-duration-in-dax/
I recommend you post your question on the power bi community to get further help. CZcams comment box is not ideal for this.
/Ruth
Hi Ruth! I cant find the PP utilities to download. Could you please send me the link to the video?
If you search on google “pp utilities” you will find tons of links!
/Ruth
thanks for the video. very nice. What is the difference between referencing a column like [this] or like Table[this] ? thanks so much
Hi Paul,
This is an unwritten rule that most Power BI uses follow to make DAX code easy to read and it goes like this:
“Refer to columns as table[columnName] and refer to measures as [measure] (without table name).
Doing it this way it is easy to see what is a column and what is a measure.
/Ruth
@@CurbalEN very nice!! Thanks so much. Just subscribed to yourr amazing channel. Another thing: How would you count unique values in col A where colB>10 ? is that using COUNTROWS + Filter? thanks so much again.
I'm trying to first bring a list of ROWS where Col B>10 to then distincount col A from this list. But can't make it happen.
Welcome to the channel!!
For personalize support, would you mind posting the the power bi community? I get too many questions !
/Ruth
@@CurbalEN of course! thanks for that.
Suppose you have to calculate the number of digits in each row in a column (say stock quantity, first row is 123) and make a column for the number of digits. So that the new column has the 1st row as 3 because '123' has 3 digits. how can you do it in Power Bi?
Hi Ruth, thank for ur video. I have a special case need ur help. I have a table with Article Code, Selling date, qty and amt, in which there are some article with sold qty -1, coz returned items. How can i do distint count with sold out qty is greater than 0. Thank you
+viet vuduc Hi viet, try this:
Calculate(distinctcount(table(article nr), table(qty)>0)
/Ruth
Oh, Thank you, Ruth. I got it and I have done it. Thank you so much
+viet vuduc Great! Glad it worked!
/Ruth
very comprehensive
Awesome!
/Ruth
Excellent tutorial, Thank you.
A humble suggestion for improvement - better your audio by moving the mike away or consider a separate mike.All the best.
+Prakash Michael Thanks Prakash, I will !
/Ruth
Hello
I am applying DISTINTCOUNT in power BI to count the bills, debits and credits but the answers are not correct.
In PivotTables I find myself using it when I set up the DISCONTINUOUS field. Help me with Email to send the file to you.
Thank you
+Ricardo Tito Hi Ricardo, please post your question in the Power bi Community. Post some sample data to help troubleshoot :)
/Ruth
Hi Ruth, CountX can also count Text values. How is it different from Countax then
@Aayush Saxena
COUNTX and COUNTAX are identical in DAX for all the data types except Boolean. COUNTAX can operate on a Boolean data type, whereas COUNTX cannot do that.
Reference : dax.guide/countax/
@@HarshNathani thanks man..will check
We can use same functions in dax
Hey, count can be used to count text right? Atleast in New version? Am i correct? 🙄
I tried with text data type and is fine.. Or am i doing something wrong?
Hi Ruth, How are you doing.
I need your help with DAX. I have 3 tables: table D_STORELIST has columns: STORECODE/STORENAME. Table D_MASTERFILE has columns: PRODUCTCODE/STYLECODE. Table F_SOH has columns: STORECODE/PRODUCTCODE/QTY.
Table D_STORELIST connect with F_SOH by column STORECODE.
Table D_MASTERFILE connect with F_SOH by column PRODUCTCODE.
I want to do distinct count Style code by Store so I used CALCULATE(DISTINCTCOUNT(D_MasterFile[StyleCode]),F_SOH[Qty]>0,F_SOH[StoreCode]) but not work. Do I have something wrong here rite?
Can you pls help me to fix it? Thanks you
Viet
Hi Viet! Could you post in the power bi community instead? It is a better place for getting support. Thanks!
/Ruth
Your countx confused me, where you filtered the table part of the expression:
= COUNTX(FILTER(ProductData, ProductData[StockQty]>100,ProductData[StockQty])
I would have used calculate with a filter:
= CALCULATE(COUNTX(ProductData, ProductData[StockQty),ProductData[StockQty>100)
I'm pretty new to DAX. Is there any reason to use either method over the other?
Stay tuned for the new series dropping this week, it will explain it :)
Hi,
I am not able to download this excel file from the location you have mentioned.
Thanks
What error are you getting?
/Ruth
Thank you for this great video. But suppose I have a text-string and I want to count how may times a specific word the that column contains that specific word, like for example I have a table with a column that contains these tesxtrings: DTFM=00090##àç!uhkjkkk##ANWSERED##DEST=092416273##
How can I count the strings that contain "ANWSERED" ?
czcams.com/video/3N0udB2GDE8/video.html
:-)
You mentioned as least liked video and now it appears as suggested video :) People are curious. People wanna see what was wrong with it, or did it have any wrong :)
+Emin Uzun oh is it? Really?
In that case, feedback is welcome as to what is wrong so I can fix it on the recap video!
I have to check now!
/Ruth
Ruth is asking help from Ruth or is it a glitch?
+Emin Uzun Mmmm maybe a prank? Parallel universe? Multiple personalities?
🤔
The mystery continues....
/Ruth
Multiple personalities I assume... Biker, hiker and BIer :) It returned to normal now ;)
+Emin Uzun ..and the worst of all, there is more layers in this onion 😂😂
Oh dear!
/Ruth
Tjena Ruth. You might want to fix a few typos in the title for this. Hope you don´t mind me pointing that out :-)
Hej Ragnar, 😂 Fixed ...och tackar! :)
/Ruth
Hope you don´t mind me asking a favour. I´m looking for the video where you changed the Quick Access toolbar to include the most common buttons. Tried your guide to finding content but still no joy. Så om du skulla vära så snäll?
Självklart, här är den:
czcams.com/video/jHcwjitKtHw/video.html
Ska även lägga "Quick Access toolbar" till beskrivningen så det går att hitta. ;)
/Ruth
I the file still available for download?
+alan bramble Yes, it is. I am just having problems with my server.
Send me an email here and I will send it to you: Curbal.com/contact
/Ruth
Hii madam,
Could you please make video on running count ?
Hi,
Have you seen this one?
/Ruth
@@CurbalEN
Thanks for reply,
Could you please suggest how to calculate running count in measure or calculate column ?
Example =countifs($B3$: B3,B3 )=1 then calculate sale
Copy down
Regard
Rahul shewale
sorry, but i did not understand why we are doing it in excel not in power bi. i tried count function both as measure and calculated column in power bi and it is gives the count in both cases. Could you please clear my confusion !
Hi! I was asked to do it in excel back then, that is why some videos are in excel.
There is no difference between power Bi and excel, do you issue is probably in the model.
Put the question in power bi and give as much info as possible to get help.
/Ruth
@@CurbalEN Hi , Thanks for your reply.
1. I tried the formula countx on productname column in excel but shows error ,while if i do the same formula in power bi ,it gives the result to 77 product name. formula is
COUNTX(Products,Products[ProductName]) .
2 not able to understand
COUNTX(Table1,[UnitPrice]>50) shows error
while COUNTX(FILTER(Table1,Table1[UnitPrice]>50),Table1[UnitPrice]) gives solution.
i didnot get it as countx says first give table name and then expression. so if i talk about error formula what is wrong , i want to show me table1 where unitprice >50 ? could you please explain this also !
Lastly i would request you to please teach using power bi ,becasue some videos i saw in excel ,some are in dax studio.
All my videos nowadays are in power bi :)
For support, please contact the Power Bi community:)
/Ruth
Many faults were captured, please review the function COUNT, it count both numbers and text types of data, while COUNTA takes boolean in addition.
Thanks will do?
listening to the audio is extremely patience testing! slowww narration and mic too close
+rigoukitke Aha! Maybe that is why this is the most disliked DAX video ...
Don't worry newer videos are better ;)
/Ruth