Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
amotto11
Helper II
Helper II

Countifs Function

Hello, I am pretty new to PowerBI, and I am trying to write a DAX expression for a column that would use Excel's version of countifs. Below is my data, the final column would be the output. The formula i am using in excel to acheive column 3 is COUNTIFS($A$2:$A$13,A2,$B$2:$B$13,1). Can someone help me build this column in PowerBI with DAX? Basically i am trying to create a filter that gives me only the quotes with companyid 1 included. Thanks in advance.

 

QuoteID       CompanyID          CalculatedColumn

60525                    1                                1

60525                  1245                            1

60525                  1375                            1

60525                  1475                            1

223877                1245                            0

223877                1375                            0

223877                1475                            0

223877                1758                            0

223877                1968                            0

458623                1968                            1

458623                    1                              1

458623                 1275                           1

458623                 1258                           1

458623                 1678                           1

1 ACCEPTED SOLUTION

I was able to get it. The table i created was from the following formula, then i just linked it to my existing table and created a slicer on the QuoteID and selected all except blank. Thank you for your help!

 

=DISTINCT(FILTER(table,table[CompanyID]=1))

View solution in original post

14 REPLIES 14
Ashish_Mathur
Super User
Super User

Hi @amotto11,

 

Try this calculated column formula

 

=CALCULATE(COUNTROWS(Data),FILTER(Data,Data[QuoteID]=EARLIER(Data[QuoteID])),Data[CompanyID]=1)

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
alena2k
Resolver IV
Resolver IV

Try to use calculated column instead of measure, it will allow you to use all possible filters in visuals:

 

x =
CALCULATE(
COUNTX('tbl', DISTINCTCOUNT(tbl[QuoteID])), 'tbl'[CompanyID] =1
)

Thank you for your help, but unfortunatly that was that same result as the first reply on this thread. it has a 1 where the company is 1 but not when the quote contains the company 1 by the company is not 1.

If the end goal is to simply get a count of how many items have a CustomerID of 1, you could do this:

 

1. Create a table of unique QuoteID's

2. Relate this to your other table, 1 -> *

3. Create the following columns in your new QuoteID table with only unique QuoteID's. In my formulas, quotes is the original table you presented and quotes2 is the one with only unique QuoteID's

 

Column = CALCULATE(COUNT(quotes[CompanyID]),RELATEDTABLE(quotes))

Column 2 = COUNTX(FILTER(RELATEDTABLE(quotes),[CompanyID]=1),[CompanyID])

Column 3 = [Column]*[Column 2]

You can now simply SUM [Column 3] to get your number.

 

Again, without the real reason around what you are trying to accomplish, not sure if this solution will work for you.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

I think you are on the right track of what i would like to do, and you have possibly given me another solution, but can you help me with one thing. Can i create a table of unique QuoteID's where CompanyID = 1. If i can do this, then i can link this to my other table and i wouldn't need any formulas, i would just be creating an inner join of sorts, and all the QuoteID's that do not contain Company 1 then would be removed.

 

I don't know what is needed to create a table, it looks like i can use a formula though.

I was able to get it. The table i created was from the following formula, then i just linked it to my existing table and created a slicer on the QuoteID and selected all except blank. Thank you for your help!

 

=DISTINCT(FILTER(table,table[CompanyID]=1))

Awesome! Happy to help you get to a solution!


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
alena2k
Resolver IV
Resolver IV

Try to use calculated column instead of measure, it will allow you to use all possible filters in visuals:

 

x =
CALCULATE(
COUNTX('tbl', DISTINCTCOUNT(tbl[QuoteID])), 'tbl'[CompanyID] =1
)

Greg_Deckler
Super User
Super User

Measure = CALCULATE(COUNT([QuoteID]),[CompanyID]=1)

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

smoupre, thank you for your response. Unfortunatly, that is only giving me 1's where the companyID is 1, not the full QuoteID if a company is 1. Basically it is just giving a 1 in the same rows that the company is a 1. I would like it to give a 1 in any row if the company 1 shows up in the QuoteID.

Omega
Impactful Individual
Impactful Individual

Try the below measure: 

 

Measure = IF(CALCULATE(DISTINCTCOUNT(Table1[Quote ID]),Table1[Company ID]=1)=0,0,CALCULATE(DISTINCTCOUNT(Table1[Quote ID]),Table1[Company ID]=1))

Thank you for your response, but unfortunatly that was the same result as the previous response, but instead of Blanks it put 0's. Basically it has a 1 where there is a 1 for the company, but 0's everywhere else. I would like to have a 1 be on every row where the QuoteID contains the 1 company. So even though the company on that row isn't a 1, if the QuoteID contains the 1 company, then that row should be a 1.

I think I understand what you are going for now. I'll take a look at how to do that. One question though is why do you feel this is what you need? I'm not understanding the use case behind it. There may be different solution if I could conceptualize what you are trying to do exactly.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Sure, i understand that. This is just my solution to the problem i have, but if you have a better one then that would be great. First off, unfortunatly i lost my link to my data so i only have the data in the PowerBI workbook, so nothing can be done outside of PowerBI. Normally i would handle this in SQL but since my raw data has been wiped i cannot do that.

 

I am trying to filter my data to only look at the quotes where they contain company 1. So basically i am creating a column that will let me filter my data, thereby filtering all my ranking function so that i am only looking at quotes where they contain company 1. I really would like to delete any of the quotes that don't have company 1 included, but i don't know how to do that in Power BI. Like i said, creating a column to filter on was the only solution i could come up with, and i figured the formula would be fairly easy to write, since it is easy in excel. I hope this helps you visualize what i am trying to do. Please keep in mind that the table here is around 5mm rows and has far more columns than I provided.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.