CountIF in Power BI using DAX

In this video I’ll be showing you different ways of writing excel like COUNTIF in Power BI using DAX.
Read the full post here - www.goodly.co....
- - - My Courses - - - -
✔️ Mastering DAX in Power BI -
goodly.co.in/l...
✔️ Power Query Course-
goodly.co.in/l...
✔️ Master Excel Step by Step-
goodly.co.in/l...
✔️ Business Intelligence Dashboards-
goodly.co.in/l...
- - - Blog - - - -
www.goodly.co....

[@GanatraNilesh]

Simply the best. Simplest way of explaining without any bragging.

[@master_blizzard]

This video saved me. I spent DAYS looking for ways to sort by values a pivot table with multiple rows. I used this formula on the Data Manager of Power Pivot and voilà! Thank you so much.

[@GoodlyChandeep]

Glad it helped "

[@pravinshingadia7337]

Your videos are absolutely brilliant. Really well explained and they go through more complex aspects of DAX in a simple manner. Thank you for your work.

[@nonoobott8602]

Learned so much from your videos. Thanks for sharing

[@GoodlyChandeep]

Glad it was helpful!

[@tremblaycam95]

Exactly what I needed! Thanks!

[@prasannajayashankar]

This was definitely helpful. Thank you

[@GoodlyChandeep]

Glad it was helpful!

[@rahulkalingeri1206]

Thanks a lot for this :-) I was creating a separate query and using GroupBy function to find the no. of repeated rows in the column. With this I was able to add a calculated column directly in power pivot Thanks a tonnnnn

[@mauricio1432010]

Very well explained, Goodly videos are always the best.

[@surajchavan6447]

As Always Awesome !!!

[@GoodlyChandeep]

Thank you!

[@chiradipbhattacharya5461]

Brilliant video as always. I have a scenario where the number of allocations (based on the value selected by user) for a person in a project needs to be displayed over a 3 year span. Now user can select the required 3 year span and all of it is dynamic. User is also able to select the allocation% above which the count needs to be made. However, the only problem is how to count that and put it inside the matrix visual. Please let me know if you have any suggestions on this. Thanks.

[@ahmed007Jaber]

Thank u for this this is really helpful Trying to do a scatter plot, bell curve but I need it to have a small number of buckets that should update as i change products. Want to so it on say quantities of sales

[@BashirBashiri]

God bless you, man!

[@GoodlyChandeep]

Thanks Bashir

[@atlascbg]

Thank you, you Explain very well i was able to follow your instruction and get the data I need it

[@GoodlyChandeep]

Glad to hear that

[@davidblake6356]

You do great work - very good video. Many thanks.

[@GoodlyChandeep]

Many thanks!

[@nguyenthu911]

Well explained!! Thank you!

[@GoodlyChandeep]

Thanks Toni.. Glad you found it helpful

[@himkartech]

great thank you, it was easy to understand.

[@GoodlyChandeep]

Glad it helped!

[@sonerguney_GEE]

Thanks Bro! Helpfull solutions. Wish best to you!

[@GoodlyChandeep]

Glad it helped

[@sajidshaikh2727]

Good video. But I have a concern here. How to do below arithmetic function: Affiliate+Organic/Affiliate+Organic+Promotional

[@jorgealonso6339]

thanks grate video

[@GoodlyChandeep]

Glad you like it!

[@jamesdennis1187]

High-quality video and very well explained. I would love to see more Power BI DAX videos. I really like how you provide multiple scenarios. I think I have watched almost all the CountIF videos on KZbin and this for me was the most helpful.

[@GoodlyChandeep]

Thanks James!

[@vrajjindal38]

Nice video Chandeep... Can you suggest how to count agentwise number of places visited in less than 10 minutes gap through excel power query or DAX. In the table, first column contains agent name and second column contains date-time of visit.

[@mansilko]

This was really helpful. Would you also be able to explain how to calculate favorability score in engagement surveys.

[@GoodlyChandeep]

Not sure what you mean. Can you elaborate ?

[@sujkubvs]

Hi , I am looking for a Countif function in PowerBI, the column has "Late" and "On time early" under column name-Performance, Next to this column, i have Fiscal year which has 2019 and 2020, now i would like to countif the performance which has "On time early" for Fiscal year 2019

[@ephraimkizito8094]

Thanks for this, however, this is my problem: I created a measure to return the following texts "Late" and "Early" if certain conditions are met. None of these are in a calculated column, just a measure. Now I want to count how many were "Late", and how many were "Early" How do I go about it?

[@amitdubey_demon]

How we have countif in Power Bi to have the count of each repeats that is if an item has repeated 4 times then for those times the repeat count should increment like for first occurrence as 1 then 2 and so on...?

[@selvakumars8610]

Super, please explain how to use in measure?

[@viveksarma2949]

I have a column parameter (met & notmet) and row parameter (P1,P2,P3,P4) as a matrix in power bi. Now I have values for these and a total. I want to add percentage only for met next to total. When I add percentage it shows for met/ notmet/ total. I don't want that way. I just want to show percentage for met. Is it possible? How?

[@bhavyasrikavyapudi7112]

Hi, very well explained, I tried for single filter it worked well but when I am trying with multiple filters then it showing blank not sure what i am missing here. Can you please help Headcount = Var EmpID=tblHeadcountBase[LocalID] Var Selected_Date=SELECTEDVALUE('Date'[Date]) Return COUNTROWS(FILTER(All(tblHeadcountBase),tblHeadcountBase[LocalID]=EmpID&&tblHeadcountBase[AllocationStartDate]=Selected_Date))

[@TowerKing7]

Can we do the same with distinctcount?

[@riazuddin7493]

I have a column in my table name reason for leaving and based on that I want to add another column which is Volunteer attrition or Involunteer attrition i.e. if reason for leaving for an employee appears as Retired then the new column should show Involunteer attrition whereas for resignation it should show as volunteer attrition. Can you help me add this column please? Also how can I calculate the percentage of volunteer and involunteer attrition based on the total attrition for the whole year which is 1157.

[@SimranSingh-sd9yo]

I have got a plain line , I want a graph according to date. Example the sales vs purchase line in a month , that kind of graph. Also I have on column name type , which contains the values sales /purchased.

[@lguanella0212]

What if you are trying to count rows that fit within a range of numbers? I have a list of data with two columns (1: start number, 2: end number) I want to create a dynamic slicer that I can then filter on. If I pick a number in the slicer, I want the count to show the number of rows that the slicer number would fit into the range from the row. For example, If my slicer shows the number 10, I would want to count all rows that have start column before or on 10 and a end column after or on 10. So if i had a column with Start = 3 and End = 12, it should include this in the count. Any suggestions?

[@3danim8r1]

Very nicely explain great video!!, I have 1 more scenario related to Countif. For example:- if We have a date column and a particular date is repeated thrice than value should be 1,2,3. next date in same column repeated 4 times than values should 1,2,3,4. can you provide a solution on that.Let me know in case of any query.

[@GoodlyChandeep]

Please go though this post - www.goodly.co.in/countif-power-bi-dax/ the last section is on Running Count. Hope that helps

[@3danim8r1]

@@GoodlyChandeep Hi Chandeep, One more scenario related to Count. If we want to know REPEATED ProductID and ignore any productID which shows one time only. How do we do that? Regards Nitin Verma

[@hussains2898]

Useful video thanks. How can I calculate the number over overdue or due dates?

[@GoodlyChandeep]

Can you explain your case precisely ?

[@lucaspinheiro3467]

Awesome explanation. Do you know how to use a kind of "CONTAINS" instead " = " ? Thank You

[@GoodlyChandeep]

try using the CONTAINSSTRING within the FILTER function

[@Scott-uk1wv]

It would be helpful for you to show creating the column from the beginning. I have a similar task that needs to be completed, have created a custom column and receive an error when attempting to declare a variable. Are you actually creating a measure and calling it a column?

[@Scott-uk1wv]

If anyone else struggles with this where I was going wrong is I was creating a "Custom Column" in the "Edit Queries" window, which does not accept DAX but instead uses PowerQuery. In the "main" report window right click and add a column on the desired table under "Fields" to follow along with the example.

[@GoodlyChandeep]

Thanks for posting your query Scott. Yes you are right. Couple of points to help you guide further 1. In Power Query you clean up and shape your data and then take it to Power BI by clicking on Close and Apply in the Home Tab. 2. After the data lands in Power BI we start doing calculations which can be either creating column or writing measures (preferred over creating columns) Let me know if you have any questions. Thanks Chandeep

[@williamsbaster1843]

Thanks

[@GoodlyChandeep]

Welcome!

[@shubhabratadey]

Can anyone here help me if I require countif in the way like the 1st record will give 1, 2nd matching will give 2, so on and so forth? Also need this on 1 specific column and on multiple columns?

[@NurulFirmanMisran]

Hell there! Great Video! I have a challenge i have a table with contains dimensions.. but i want to create a dynamic indicator... Campaign | Buy Type | 1 | A 1 | B 2 | A 2 | B 3 | B 4 | A I want to summarise the table to Total Campaign | Does Buy Type A Exits 3 | Yes 1| No Then I need to summarize further into percentage to find total number of campaign which contains Buy Type A which 75% How do I do that? Thanks! Firman

[@GoodlyChandeep]

Hey somehow I din't get notified of your comment. Created a little file for you - chandeepchhabra-my.sharepoint.com/:u:/g/personal/chandeepchhabra_chandeepchhabra_onmicrosoft_com/Ea7PG-EC3L9MjxJ3gZMZ_wUBUhzbHgMW68qw-_1oFkjNmQ?e=cdhGBy

[@rahulshewale9206]

Hi sir, Thanks for video ,very useful How add running count in dax (calculate column ) Thanks suggestions

[@GoodlyChandeep]

Hi Rahul, You'll have to create an Index (serial number) column in Power Query and write a small DAX formula. I have included a picture to here to help you. Take a look - chandeepchhabra-my.sharepoint.com/:i:/g/personal/chandeepchhabra_chandeepchhabra_onmicrosoft_com/ESBW0qjHPtxFkOqK-rryGQwBQTK4n8tE_TtDGj0RFL0g0Q?e=MxoCKk

[@matheusmascarenhas]

How to know when I should choose a Calculated Measure or a Calculated Column for this COUNT IF in DAX?

[@GoodlyChandeep]

That question definitely calls for a separate video. But here is the quick answer - If you are starting out with DAX keep a general rule in Mind. For all Calculations - Use Measures For all Dimensions (Chart Axis, Pivot Rows or Col Values, Slicers) - A column Hope this helps, Cheers

[@NeerajSharma-i7]

i am working on netflix dataset having movie names, their country of origin,genre,rating etc. Now the issue here i am facing is the genre column has string values (multiple texts like, action, thriller,drama,crme) in a single cell for any given movie/show. Now if i want to count each individual text from genre from any given country, how will i achieve that.

[@GoodlyChandeep]

You'll need to split the genre in multiple rows. You can easily do that in Power Query - Split by Delimiter and use Split in Rows

[@NeerajSharma-i7]

@@GoodlyChandeep thanks for reply, i have already done that, but now the individual texts are distributed in multiple columns. for eg 'drama' is distributed in 'n' numbers in 3-4 columns, and similarly others too. how to count them. can we make a separate table, with distinct genre in one column and their count from previous table's multiple columns be shown next to them.

[@zvikabar-kochva3641]

Thanks Gooly, very useful clip. What about if I want to do countif against some value stored somewhere in my excel file. Say my table is holding all sales and I would like to count only the sales during the last running 12 months. Suppose column 2 holds the sales date and cell A2 holds the update date of the sales table. I would like to count all sale occurring during 365 days prior to the update date, such as Sales last 12 months:=Countif(A2-sales [date]

[@GoodlyChandeep]

I am sorry I din't quite grasp the data? Can you elaborate or send a quick data sample to my email - [e-mail]

[@zvikabar-kochva3641]

@@GoodlyChandeep Sure. I'll do that shortly.

[@adeelmunir6019]

How can we do Countif and Countifs in m language rather than Dax

[@vinodkumarhs4000]

Formula to count no of rows greater to some date

[@Multijuice17]

How do you replace && with or. so counts both at the same time?

[@GoodlyChandeep]

Instead of writing "&&", write "||" (double pipe symbol) above the enter key. That means OR. hope it helps

[@yvettesilen6251]

how to calculate start date and end date not include blank cells Basically, I'd like to calculate the difference between start and end date, but leave the cells in the calculated column blank if there is no end date. I can't do "" because I get the error saying that you can't mix integers and strings. I can do 0 instead but that makes my calculations after this more difficult. Is there a way to say something like "null" to leave it blank? In excel would be this Time = IF(ISBLANK([startdate]); ""; [enddate]-[startdate])

[@GoodlyChandeep]

Write the same differently = IF ( [StartDate] BLANK (), [EndDate] - [StartDate] ) Hope this helps

[@yvettesilen6251]

@@GoodlyChandeep thanks but then a lot of them are giving me -wholenumbers. I dont want it to have -whole numbers on the column . The ones that done have end date the column with Days to reply giving -43958. I do not want Negative numbers

[@GoodlyChandeep]

@@yvettesilen6251 = IF ( [StartDate] BLANK (), MAX ( [EndDate] - [StartDate], BLANK() ) )

[@yvettesilen6251]

@@GoodlyChandeep PERFECT EQUATION !

[@domnorth952]

Great video! I have used the countif variable formula, and it worked. However, i have a further question; i want to count the totals be filterable by a slicer I.e. if I select September, i want the number of rows counted specific for September, and not just the overall total. Is this possible? Thanks

[@domnorth952]

I appreciate the video, and any help is much appreciated.

[@GoodlyChandeep]

Create a Measure Count Calculation = COUNTROWS(TableName) And create a slicer on Month.. it should work

[@domnorth952]

@@GoodlyChandeep apologies, but will this return the countif values? The formula in the video gives me the overall total, but when applied to a slicer I don’t get the total for that specific month, but rather the overall total. I hope this is clear, and i will try your recommendation tomorrow, and let you know.

[@domnorth952]

Thanks 😁

[@GoodlyChandeep]

@@domnorth952 Hi Dom, Any calculation becomes a COUNTIF / SUMIF when a slicer is selected. Can you send me a dummy dataset for your exact problem? I'll take a look [e-mail] Thanks

[@marekplsko1316]

Hi, I'm trying to see if a table appears repeatedly in my spreadsheet within three days. In Excel, I solved it with the countifs function, but I can't do it in Dax. So far I have this formula: = IF (CALCULATE (DISTINCTCOUNT ('Table1' [Value]); FILTER ('Table1'; 'Table1' [Value] = EARLIER ('Table1' [Value])); Filter ('Table1'; 'Table1' [Date]> EARLIER ('Table1' [Date]))); 1; 0). The unit is the flag for a repeat and 0 for a non-repeat value. I don't know how to get the condition of repeating there for a maximum of 3 days. Can you help me please?

[@GoodlyChandeep]

Seems not too difficult to solve. Can you share some sample data and describe the problem in detail ? [e-mail]

[@marekplsko1316]

@@GoodlyChandeep Thank you, I sent you an email..

[@SonuSonu-uh2er]

I want to count rows of a colounm where only yes value is present

[@GoodlyChandeep]

Drag that column to the rows of the pivot table Add this measure Calculate ( countrows(table), keepfilters([column with yes no] = "yes" ))

[@akashtribhuvan8124]

How can we do the same in power query?

[@GoodlyChandeep]

Group By in the Transform tab

[@RohitThakur-ku8sb]

Can you plz help to do this in power query as well

[@GoodlyChandeep]

Use group by in Power Query

[@RohitThakur-ku8sb]

@@GoodlyChandeep It worked for me🤟. Thanks a lot 👍

[@devallamahesh989]

Good vdeo but how to do in measure

[@jaitiwari241]

It was helpful.. But without dataset there is no use

[@GoodlyChandeep]

Glad it was helpful 😊

[@jaitiwari241]

atleast provide dataset to practice for ur you tube videos.. otherwise it is worthless. sir