Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 formula). However, whenever I try applying any count related filter I got the problem shown in the image below. How do I apply a count rows filter (or any function alike) to do so? Any thoughts?

Thanks in advance.

Best regards,

- Pedro H.

pedrohp503_0-1652030461263.png

1 ACCEPTED SOLUTION
CNENFRNL
Community Champion
Community Champion

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] )
    )
)

 


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

5 REPLIES 5
CNENFRNL
Community Champion
Community Champion

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] )
    )
)

 


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Anonymous
Not applicable

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

jdbuchanan71
Super User
Super User

@Anonymous 

You can make a variable in your measure that is the list of people with their count, then filter that list and use the filtered list in your calculate.

 

 

Count Rows Filtered = 
VAR _List = 
FILTER(
   ADDCOLUMNSS(
        DISTINCT(Base[Assignee]),
        "@Count",CALCULATE(COUNT(Base[ticket_number]))),
        [@Count] > 7
)
RETURN
CALCULATE(
   DISTINCTCOUNTNOBLANKK(Base[ticket_number]),_List
)

 

The VAR _List has the list of people with a count > 7 then the _List is used as a filter in the CALCULATE

 

Anonymous
Not applicable

Appreciate your reply, @jdbuchanan71, but it didn't work. Is there any way I can do it inside the formula I showed in the image above?

Yes, add the VAR that generates the list and use it in your CALCULATE.  It is easier to work with your formulas if you paste them as text rather than a screenshot.   You can paste them in a codeblock using this tag:

jdbuchanan71_0-1652036193095.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.