COUNTIF in DAX for Power BI Card

Question

Here is my sample table (for this report I'm using only one table, so there is no table-relationship-links to contend with):

Cost Centre	Project	Invoice Approver
123	AB	Person One
123	AB	Person Two
123	ZZ	Person One
456	TB	Person Three

I have a measure already written Approver = COUNT('Table'[Invoice Approver]). In the sample above, Approver = 4.

I have created a new table showing only those combinations with 1 approver. The table shows 123|ZZ and 456|TB (NOTE: for this table, the approver(s) do not need to be shown, only the unique cost centre/project combinations).

My next step is to have a Card showing the net quantity of the filters: 2. But I need help in writing the DAX measure.

I have tried the following DAX formulae:

Code	Result
`1InvApp = COUNTROWS(FILTER('Table',[Approver]="1"))`	"DAX comparison operations do not support comparing values of type Integer with values of type Text."
`1InvApp = COUNTROWS(FILTER('Table',[Approver]=1))`	Blank (text)
`1InvApp = CALCULATE(COUNTROWS('Table'),[Approver]="1")`	"A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression."
`1InvApp = CALCULATE(COUNTROWS('Table'),[Approver]=1)`	"A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression."
`1InvApp = COUNTX('Table',[Approver]="1")`	"DAX comparison operations do not support comparing values of type Integer with values of type Text."
`1InvApp = COUNTX('Table',[Approver]=1)`	"The function COUNTX cannot work with values of type Boolean."
`1InvApp = COUNTAX(FILTER('Table',[Approver]=1),'Table'[Approver])`	Blank (text)
`1InvApp = COUNTAX(FILTER('Table',[Approver]="1"),'Table'[Approver])`	"DAX comparison operations do not support comparing values of type Integer with values of type Text."
`1InvApp = COUNTROWS(FILTER(ALL('Table'),[Approver]=1))` (as suggested here)	Blank (text)

QUESTION: What is the correct DAX syntax to show the desired total result, 2?

Thanks in advance.

What is the meaning of net quantity of filter equal to 2 or approver = 1? — Kin Siang
My data has 4 lines. When I filter to only show combinations with a single invoice approver, there are 2 lines which show. My Card needs to show 2. — Marc
You also ask about approver = 1. Are you taking this from the formulae? [Approver] = 1 is my attempt to determine the quantity of invoice approvers for each combination of cost centre/project. the [Approver] measure is taken from COUNT('Table'[Invoice Approver]) — Marc

msta42a msta42a · Accepted Answer · 2021-06-03T12:49:24

You can do this in two steps: First:

Measure = CALCULATE(COUNT('Table'[Invoiced]), FILTER(ALL('Table'), SELECTEDVALUE('Table'[Cost Centre]) = 'Table'[Cost Centre] && 'Table'[Project] = SELECTEDVALUE('Table'[Project])))

Secod:

OnlyOne = CALCULATE(COUNTROWS(CALCULATETABLE('Table', FILTER('Table', var __xx = [Measure] return [Measure] = 1))))

COUNTIF in DAX for Power BI Card

1 Answers