COUNTROWS with multiple FILTER(FIND) criteria

[jagostinhoCT]

Found this post on how to use multiple criteria but am not being able to make it to work when using the FILTER together with the FIND function.

What am I doing wrong?

I want to be able to count the number of rows that match BOTH criteria, i.e., cell contains "Family Category and Parameters" AND ends with ".rvt"

CAD Family Category and Parameters (RVT) Count = CALCULATE(
COUNTROWS('KBT Logs - Logs')
//Filtering all ".rvt"
FILTER('KBT Logs - Logs',FIND(".rvt",'KBT Logs - Logs'[LogInfo],,0)),
//Filtering all "Family Cat Param"
FILTER('KBT Logs - Logs',FIND("Family Category and Parameters",'KBT Logs - Logs'[LogInfo],,0)))

Thank you in advance.

J

[v-jiascu-msft · Accepted solution]

Hi @jagostinhoCT,

It should work. Can you share a dummy sample of your data? 

Did you get any error messages? Or Just you got a wrong result?

BTW, you can put two conditions in one place like below.

CAD Family Category and Parameters (RVT) Count =
CALCULATE (
    COUNTROWS ( 'KBT Logs - Logs' ),
    FILTER (
        'KBT Logs - Logs',
        FIND ( ".rvt", 'KBT Logs - Logs'[LogInfo],, 0 )
            && FIND ( "Family Category and Parameters", 'KBT Logs - Logs'[LogInfo],, 0 )
    )
)

Best Regards,

Dale

[jagostinhoCT]

Thank you @v-jiascu-msft.

Here is my final formula for the new added column. BTW, I have added the column outside of the query. Not sure if this is best practice or not but I found it easier to do it this way.

ActionGroup = IF(
//Criteria set for CAD Processes
'KBT Logs - Logs'[WindowName]="Import CAD Formats" ||
'KBT Logs - Logs'[WindowName]="Name" ||
('KBT Logs - Logs'[WindowName]="Family Category and Parameters" && 'KBT Logs - Logs'[File type]="rvt") ||
'KBT Logs - Logs'[WindowName]="Create Detail Group" ||
'KBT Logs - Logs'[WindowName]="New Drafting View",
"CAD Processes",

//Criteria for Families
IF(
'KBT Logs - Logs'[WindowName]="New Family - Select Template File" ||
('KBT Logs - Logs'[WindowName]="Family Category and Parameters" && 'KBT Logs - Logs'[File type]="rfa") ||
('KBT Logs - Logs'[WindowName]="Parameter Properties" && 'KBT Logs - Logs'[File type]="rfa"),
"Families",

//Criteria for Project Setup
IF(
'KBT Logs - Logs'[WindowName]="Create Model Group" ||
'KBT Logs - Logs'[WindowName]="Design Options" ||
'KBT Logs - Logs'[WindowName]="Import/Link RVT" ||
'KBT Logs - Logs'[WindowName]="Link CAD Format" ||
'KBT Logs - Logs'[WindowName]="Load Family" ||
('KBT Logs - Logs'[WindowName]="Parameter Properties" && 'KBT Logs - Logs'[File type]="rvt") ||
'KBT Logs - Logs'[WindowName]="Phasing" ||
'KBT Logs - Logs'[WindowName]="Select Items To Copy",
"Project Setup",
BLANK())))