Forum Discussion
Emulating Excel CountIfs
Hi - I am realy struggling with this, have seen a number of posts that almost solve it but I just can't get it to work.
Difficult to explain why I need this without writing an entire essay, but below is the functon in excel I am trying to emulate
| A | B | C | D | E | F |
| Connection ID | Queue name | Count of IVR in C | Count of non Null in C | Flag Where IVR is the only Non Null | |
| Formulas ==> | =COUNTIFS(C:C,"IVR",A:A,A3) | =COUNTIFS(C:C,"<>" & "",A:A,A3) | |||
| 1 | IVR | 2 | 6 | ||
| 1 | IVR | 2 | 6 | ||
| 1 | A | 2 | 6 | ||
| 1 | A | 2 | 6 | ||
| 1 | B | 2 | 6 | ||
| 1 | B | 2 | 6 | ||
| 2 | IVR | 3 | 3 | IVR Only | |
| 2 | IVR | 3 | 3 | IVR Only | |
| 2 | IVR | 3 | 3 | IVR Only | |
| 2 | 3 | 3 | IVR Only | ||
| 2 | 3 | 3 | IVR Only | ||
| 3 | IVR | 2 | 3 | ||
| 3 | IVR | 2 | 3 | ||
| 3 | 2 | 3 | |||
| 3 | C | 2 | 3 |
I would like to get the 2 Counts ( in Coumns D & E where I have shown the Excel formula) into my query data model so that I can do a number of manipulations on my data. The dax formula that I attempted to use to create the 1st count was:
IVRCount1 = CALCULATE (
COUNTROWS ( AB_Partner_Report ),
AB_Partner_Report,
AB_Partner_Report[networkConnIDAB] = AB_Partner_Report[networkConnIDAB]
& (AB_Partner_Report[queue] = "IVR") = TRUE)
But the message "Expression.Error: The name 'CALCULATE' wasn't recognized. Make sure it's spelled correctly."
I attempted to revise the formula as:
COUNTROWS ( AB_Partner_Report ),
AB_Partner_Report,
AB_Partner_Report[networkConnIDAB] = AB_Partner_Report[networkConnIDAB]
& (AB_Partner_Report[queue] = "IVR") = TRUE
But this fails syntax every time it encounters a comma
5 Replies
AlanMB It's just COUNTROWS( FILTER( ... ) ). No need for CALCULATE: https://www.bing.com/ck/a?!&&p=4b85c7bfe4058b3042fd2580e9e3691dbda3154ccf06d3f9900277ed5255ca6cJmltdHM9MTczNDU2NjQwMA&ptn=3&ver=2&hsh=4&fclid=2677b933-764f-6a16-3d22-ab7c779c6b62&psq=excel+to+dax+translation&u=a1aHR0cHM6Ly9jb21tdW5pdHkuZmFicmljLm1pY3Jvc29mdC5jb20vdDUvUG93ZXItQkktQ29tbXVuaXR5LUJsb2cvRXhjZWwtdG8tREFYLVRyYW5zbGF0aW9uL2JhLXAvMTA2MDk5MQ&ntb=1
Hi Greg
so I tried COUNTROWS (Filter( AB_Partner_Report ),
AB_Partner_Report[queue] = "IVR",
AB_Partner_Report[networkConnIDAB] = AB_Partner_Report[networkConnIDAB]
)The error is now "Expression.Error: The name 'COUNTROWS' wasn't recognized. Make sure it's spelled correctly."
To be clear I am trying to add a column. A collegue has suggested adding a measure, but I am not sure how this will work as my current excel solution relies on combinng the 2 extra columns to generate a 3rd column. Can I create a new column based on 2 measures?
Hi AlanMB ,hello Greg_Deckler, thank you for your prompt reply!
The second argument to the CALCULATE function should be a logical condition, not a table name.
Please try the following dax:IVRCount = CALCULATE ( COUNTROWS ( AB_Partner_Report ), AB_Partner_Report[queue] = "IVR", AB_Partner_Report[networkConnIDAB] = AB_Partner_Report[networkConnIDAB] )Best regards,
Joyce
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi v-yajiewan-msft
I still get the message ""Expression.Error: The name 'CALCULATE' wasn't recognized. Make sure it's spelled correctly."
Hi AlanMB,
Ensure you're not using CALCULATE in the Power Query Editor.
CALCULATE is a DAX function and should be used in calculated columns or measures within the Data Model, not in Power Query (which uses M language).Best regards,
Joyce
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
