Conditional Column with Match and Specific Text Criteria

[CMoppet]

I have two tables of data; 'BaseSummary' and 'WO Raw Data'.

The first table is a list of all our different machines types and a count of how many we have of each.

The second table contains technician visit data, with each row representing a technical visit for either a repair or a planned maintenance call.

In the 'BaseSummary' table I am trying to add a column that counts rows in 'WO Raw Data' based on two conditions;

1) The row must say 'Repair' in the column called 'MTBF Repair?'

2) The value in 'Machine Type' column must be the same in both tables

3) The value in 'Month/Year' column  must be the same in both tables

Essentially, I'm trying to calculate MTBF.  My BaseSummary table is giving me the total operational hours for each machine type, each month (the active base varies on a monthly basis).  I now need to bring in to it the number of failures for each machine type each month.

So far, I have this written to accommodate the first condition, but I'm struggling to add the second and third conditon.

No. of Failures = countrows(filter('WO Raw Data',[MTBF Repair?]="Repair"))

Please can someone help?

Thank you 🙂

[CMoppet]

I've solved this now, using the idea above to concatenate: 

No. of Failures = COUNTROWS(FILTER('WO Raw Data',[MonthYearMachineType]=BaseSummary[MonthYearMachineType] && 'WO Raw Data'[MTBF Repair?]="Repair" ))

[newellaa · Accepted solution]

Do you have a join defined between the two columns in the model view? It seems a concatenation of Machine Type and Month/Year columns is necessary in each table in order to join the tables on the new concatentated field.

[CMoppet]

That makes sense.  So if I created a concatenated column first, how would I then create the new column that countsrows where the new concatenated column values match, AND the row in 'WO Raw Data' says 'Repair'?

How do I add that 'match' part to the original statement:  

No. of Failures = countrows(filter('WO Raw Data',[MTBF Repair?]="Repair"))

[mostho]

i am not sure about your data model. If BaseSummary is a "list of all our different machines" i would expect that there is one record for each machine (type).
if "the  second table contains technician visit data", so i would expect that there can be more than one record for each machine (type).

if i am right, i do not understand your third condition: "3) The value in 'Month/Year' column  must be the same in both tables"

[CMoppet]

In BaseSummary, the machine type appears once per calendar month row.  For example, there will be a row of data for each machine type for June 2024, and a row for each of those machine types again, but for July 2024.  It's difficult because I don;t have a static machine base...it varies every month.

[Ritaf1983]

Hi @CMoppet 
Try :
No. of Failures = Calculate (countrows('WO Raw Data'),'WO Raw Data' [MTBF Repair?]="Repair")
If it won't work:

please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[CMoppet]

Hello 🙂  My initial statement works fine to count the rows with 'Repair'...the bit I'm struggling with is how to only countrows where it says 'Repair' AND where the Month/Year and .MACHINE TYPE match too.  

Here's what BASESUMMARY looks like...

So I want to add a column to this that counts how many rows in the 'WO Raw Data' table have the same MACHINE TYPE, the same MONTH/YEAR, and state 'Repair' in a column called 'MTBF Repair?'.  For example, there may have been 25 'Repair' visits for KLIX OUTLOOK in JUNE 2024, so my new column should show 25 for this row.

I can't screenshot the 'WO Raw Data' table as I can't get everything in view.    I appreciate your help