Forum Discussion
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
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))
14 Replies
Measure = CALCULATE(COUNT([QuoteID]),[CompanyID]=1)
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.
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))
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
)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.
Hi amotto11,
Try this calculated column formula
=CALCULATE(COUNTROWS(Data),FILTER(Data,Data[QuoteID]=EARLIER(Data[QuoteID])),Data[CompanyID]=1)
Hope this helps.
