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!
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.
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.
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 ruclips.net/video/Sr3OdE-jx_8/видео.html Hope it helps!
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!
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
Hi Phillip, measures and calc columns are identical but not similar. Here's a video explaining how they're different: ruclips.net/video/ns4VbpRKAFs/видео.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: ruclips.net/video/OOs-VWf20E8/видео.html
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.
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.
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?
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,
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
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?
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!
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 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.
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!
Such a good video, slowly starting to become a pro on power bi. THANK YOU SO MUCH!
Amazingly explained for the refresher I needed, thanks for all the great content!
Thank you for the Countx Filter measure! Hero
Very clear explanation. Good job and thank you for sharing
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!
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!
Perfect explanation, worth to watch for every sec.
Perfect and practical explanation about the count function !!! Thank you brother
Worked like a charm!
Fantastic great to hear!
This is awesome! Your videos helps me a lot!
very clearly explained. Thank you!
Awesome explanation. You cant get better than this on this topic.
This is really helpful. Thanks
Great stuff, thanks for checking it out!
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.
Great explanation...it is much clear to me how to use these functions. Thanks.
Glad it was helpful!
Amazing explanation 😁
you explain really well
thanks for watching!
Thanks for the video. It is very helpful
You are welcome
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.
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.
thanks for the video! super helpful
Fantastic video. Thank you!
Glad you liked it!
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
ruclips.net/video/Sr3OdE-jx_8/видео.html
Hope it helps!
Great video!
Thanks for watching!
Just saved me >1 Hour
Thanks
You're very welcome!
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!
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
Excelent video sr!
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: ruclips.net/video/ns4VbpRKAFs/видео.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: ruclips.net/video/OOs-VWf20E8/видео.html
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.
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!
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.
Nice 👍🏽, new subscriber
Good stuff! Thx!
Glad you liked it!
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.
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. ..
Good one, thanks
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.
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?
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 example!
Thank you!
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
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
Thank you very much
You're welcome!
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: ruclips.net/video/vpGAf6Nkt1Q/видео.html
Very useful thanks
You're welcome, thanks for watching!
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: ruclips.net/video/qF9eKJxT5Bg/видео.html
How are you displaying the data table here? ... Are you just using the table visualisation?
Why can't we apply the distinct value count to the card visual when I create as measure
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?
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!
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
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/
count function calculates blank cells as well for me.
thank so much
just perfect tks sir
Thanks Broooo , loveit
perfect man
Thanks!
How to display the row count to each record
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?
can u xplain any project
Great!
Please share dataset
also DITINCTCOUNTNOBLANK - To avoid the blank one (:
THANKSSSSSS
awesom
Thank you!
Somehow the COUNT also counts the ZEROs in a row? Why is that the case?
Please make a video connecting Power Bi to Mongodb
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.
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.
Good video. There is some "fat" but not much
Dude! Speak way too fast!
Thanks, Fernan for your videos. Very helpful to me. How do I connect to you on Linkedln