Forum Discussion

DanC's avatar
DanC
Frequent Visitor
8 years ago

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

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?

Need Help

5 Replies

  • jthomson's avatar
    jthomson
    Icon for Solution Sage rankSolution Sage
    8 years ago

    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's avatar
      DanC
      Frequent Visitor
      8 years ago

      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

    • DanC's avatar
      DanC
      Frequent Visitor
      8 years ago

      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

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    8 years ago

    HI DanC

     

    What is that specific row field?

     

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

     

     

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Icon for Community Champion rankCommunity Champion
      8 years ago

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