Countrows Function with subsequent cell/Row value part of the criteria

[DanC]

Hi

My query is that i am used to working with excel and COUNTIFS where one of the tables values in each row is part of the criteria, so when you drag down the formula, the criteria is constantly changing depending on the cell value of the current row.

Now in PBI i have an expression 'Year 1 Callouts = CALCULATE(COUNTROWS(incidents),FILTER(incidents,incidents[Year of Life of Machine on Visit]="Year 01"),FILTER(incidents,incidents[Case Type]="Engineer Callout"),FILTER(incidents,incidents[Status Reason]="Job Complete"))'

This partly works as it totals the rows based on the criteria, but of course all the subsequent rows give the same total, and i want it the final criteria to be based on a specific row field that matches in my COUNTROWS table,

Can anyone help with the part of the criteria that i am missing?

[Zubair_Muhammad]

HI @DanC

What is that specific row field?

If you use that field with earlier function you will hopefully get correct results

[Zubair_Muhammad]

@DanC

Try this. Just replace "That_Specific_Field" with the actual field name

Year 1 Callouts =
CALCULATE (
    COUNTROWS ( incidents ),
    FILTER (
        ALLEXCEPT ( incidents, Incidents[That_Specific_Field] ),
        incidents[Year of Life of Machine on Visit] = "Year 01"
            && incidents[Case Type] = "Engineer Callout"
            && incidents[Status Reason] = "Job Complete"
    )
)

[jthomson]

I'm not sure what you're trying to do here, is there another criteria that you're wanting to add on if something matches year 1/engineer callout/job complete?

I'm also not sure why you're using the filter function as part of that expression?

[DanC]

yes the final criteria i am trying to add is where it matches the row data from one table with all the row data from the second table, so in excel it would be an 'IF' or 'COUNTIF' statement and the column header i'm trying to match is called "_new_assetid_value" from the 'incidents' table

[DanC]

Thanks for replying jtomson,

Yes i am trying to add another function as part of the lookup criteria, all the other criteria i have in the expression are using a text based lookup, i.e. count the amount of rows where the text "Yr 01" appears in a certain column, that works fine.

What i want to add as part of the lookup formula is if whatever appears in one row of a column of data of the first table, matches whatever appears in another another row of a column in my second to table. So it adds to part of the lookup criteria

Regards

Dan