Forum Discussion
Multiple FIlter CountRows of another table
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.
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] ) ) ) + 0Output:
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] ) )+0Note: 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.
3 Replies
v-jianboli-msftCommunity Support
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] ) ) ) + 0Output:
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] ) )+0Note: 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.
Worked great. Thank you so much! I also appreciated the explanation.
- Ashish_Mathur
Super User
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.
