Multiple FIlter CountRows of another table

[RockyR]

Hello,

I am struggling to get a result for COUNTROWS based on multiple criteria.

I have a table called "QuestionRef". Im trying to count the rows in another table called "LineAudit". The rows need to match [Cell] and [QID] from the QuestionRef table.

QuestionRef

Cell	QNum	QID
A	1	1
A	2	2
B	1	1
B	2	3

LineAudit

Cell	QID	Date	Status
A	1	1/1/23	False
A	2	1/1/23	False
A	1	1/2/23	False
A	2	1/2/23	True
A	1	1/3/23	False
B	1	1/3/23	False
B	3	1/3/23	True
B	1	1/3/23	True
A	2	1/3/23	False

Desired Result

Cell	QNum	QID	False Count
A	1	1	3
A	2	2	2
B	1	1	1
B	2	3	0

I tried

False Count = 
CALCULATE(
    COUNTROWS(LineAudit),
        FILTER(LineAudit,LineAudit[Status]=FALSE()),
        FILTER(LineAudit,LineAudit[Cell] = QuestionRef[Cell]),
        FILTER(LineAudit,LineAudit[QID] = QuestionRef[QID] )
)

But I keep getting the error

A single value for column 'Cell' in table 'QuestionRef' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Any help would be very appreciated.

[v-jianboli-msft · Accepted solution]

Hi @RockyR ,

It seems that your DAX Code is used in a measure? If you need a measure, please try:

Measure =
CALCULATE (
    COUNTROWS ( LineAudit ),
    FILTER (
        'LineAudit',
        [Status] = FALSE ()
            && 'LineAudit'[Cell] = MAX ( 'QuestionRef'[Cell] )
            && 'LineAudit'[QID] = MAX ( 'QuestionRef'[QID] )
    )
) + 0

Output:

Your DAX code can be used in the calculation column:

False Count = 
CALCULATE(
    COUNTROWS(LineAudit),
        FILTER(LineAudit,LineAudit[Status]=FALSE()),
        FILTER(LineAudit,LineAudit[Cell] = QuestionRef[Cell]),
        FILTER(LineAudit,LineAudit[QID] = QuestionRef[QID] )
)+0

Note: Whenever there are no rows to aggregate, the function COUNTROWS() returns a blank. Thus you need +0

Output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[RockyR]

Worked great. Thank you so much! I also appreciated the explanation.

[Ashish_Mathur]

Hi,

In the first table, write this calculated column formula

Count = calculate(countrows(Lineaudit),filter(lineaudit,lineaudit[Cell]=earlier(lineaudit[Cell])&&lineaudit[QID]=earlier(lineaudit[QID])))

Hope this helps.