Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Countrows as a filter within a CALCULATE function

Hello I'm facing a problem when filtering data inside a CALCULATE formula. What do I aim to do? To filter the people that appeared more than n times in my database (the highlighted part in my formul...
Need Help
  • CNENFRNL's avatar
    CNENFRNL
    4 years ago

    Not surprising there throws an error message. Filter 1,2,3 are based on row-level whereas filter 4 is an aggregation filter. Its SQL equivalent reads,

     

    SELECT [assignee], COUNT(DISTINCT [assignee])
FROM BASE B JOIN Teste_Agents1 T ON B.[assignee]=T.[Nome] AND B.[Setor]=T.[Setor3] --row-level
WHERE [assignee] IS NOT Null
GROUP BY [assignee]
HAVING COUNT([assignee])>7 --aggregation filter

     

    conversion of DAX measure,

     

    =
COUNTROWS(
    FILTER(
        CALCULATETABLE( VALUES( Base[assignee] ), filter1, filter2 ),
        CALCULATE( COUNTROWS( base ) > 7 ) && NOT ISBLANK( Base[assignee] )
    )
)

     

CNENFRNL's avatar
CNENFRNL
Community Champion
4 years ago

Not surprising there throws an error message. Filter 1,2,3 are based on row-level whereas filter 4 is an aggregation filter. Its SQL equivalent reads,

 

SELECT [assignee], COUNT(DISTINCT [assignee])
FROM BASE B JOIN Teste_Agents1 T ON B.[assignee]=T.[Nome] AND B.[Setor]=T.[Setor3] --row-level
WHERE [assignee] IS NOT Null
GROUP BY [assignee]
HAVING COUNT([assignee])>7 --aggregation filter

 

conversion of DAX measure,

 

=
COUNTROWS(
    FILTER(
        CALCULATETABLE( VALUES( Base[assignee] ), filter1, filter2 ),
        CALCULATE( COUNTROWS( base ) > 7 ) && NOT ISBLANK( Base[assignee] )
    )
)

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thank you very much. It was exactly what I was looking for.