Emulating Excel CountIfs

[AlanMB]

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

[Anonymous]

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.

[AlanMB]

Hi  v-yajiewan-msft

I still get the message ""Expression.Error: The name 'CALCULATE' wasn't recognized. Make sure it's spelled correctly."

[Anonymous]

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.

[Greg_Deckler]

@AlanMB It's just COUNTROWS( FILTER( ... ) ). No need for CALCULATE: https://www.bing.com/ck/a?!&&p=4b85c7bfe4058b3042fd2580e9e3691dbda3154ccf06d3f9900277ed5255ca6cJmltd...

[AlanMB]

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?