Unable to Relate Tables in the Right Way

[CMoppet]

I have two tables that I'm struggling to relate in the right way.

In one table (BaseSummary), I have a column that successully counts rows of 'soft faults' from the other table (WO Raw Data), as follows:

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

and another that counts row of 'hard faults, as follows:

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

I then have a final column that totals all the repairs together, as follows:

No. of Repairs = [No. of Hard Repairs]+[No. of Soft Repairs]

I have used this final column as part of an MTBF calculation, as follows:

MTBF = DIVIDE([OperationalWeeks],[No. of Repairs])

So far, so good. But...I have now plotted that MTBF column on a chart, with MONTH/YEAR on the x-axis, and MTBF on the Y-axis.

I want to add a slicer that allows the user to see the MTBF for either hard, soft, or all faults.  However, when I add the 'HARDSOFT' column to the slicer, it doesn't work dynamically with the chart.  I'm really stuck in knowing how to relate the tables so that it recognises the connection.  I thought it would work because I separated out the count of hard and soft when totalling the repairs in the first two columns above. 

Please can someone help me?   

The WO Raw Data table is a list of technical visits, with each row representing a visit.  

The BaseSummary table draws data from a couple of other tables, essentially building information to calculate MTBF.

I did also calculate MTBFs separately for hard and soft faults, but I really need to avoid this method as I don;t want a chart with all three MTBF lines on.  I want just the one that can then be filtered with a slicer.

Maybe the answer is not in how the tables are related, but in how I've prepared the MTBF calculation?

Any help much appreciated 🙂

[v-nuoc-msft · Accepted solution]

Hi @CMoppet 

Here I recommend that you do not create calculated columns.

Because the calculations are listed as static calculations, you may not be able to change the results dynamically when using the slicer. You might consider creating measures.

For your question, here is the method I provided:

Here's some dummy data

"WO Raw Data"

"BaseSummary"

"Operational"

Create measures.

No. of Soft Repairs = 
var _Type = SELECTEDVALUE('BaseSummary'[MonthYearMachineType])
RETURN
COUNTROWS(
    FILTER(
        'WO Raw Data',
        'WO Raw Data'[MonthYearMachineType] = _Type
        &&
        'WO Raw Data'[HARDSOF] = "Soft"
        &&
        'WO Raw Data'[MTBF Repair?] = "Repair"
    )
)

No. of Hard Repairs = 
var _Type = SELECTEDVALUE('BaseSummary'[MonthYearMachineType])
RETURN
COUNTROWS(
    FILTER(
        'WO Raw Data',
        'WO Raw Data'[MonthYearMachineType] = _Type
        &&
        'WO Raw Data'[HARDSOF] = "Hard"
        &&
        'WO Raw Data'[MTBF Repair?] = "Repair"
    )
)

No. of Repairs = 'BaseSummary'[No. of Hard Repairs] + 'BaseSummary'[No. of Soft Repairs]

MTBF = DIVIDE(SELECTEDVALUE('Operational'[Weeks]), 'BaseSummary'[No. of Repairs])

Here is the result.

Regards,

Nono Chen

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

[v-nuoc-msft · Accepted solution]

Hi @CMoppet 

Here I recommend that you do not create calculated columns.

Because the calculations are listed as static calculations, you may not be able to change the results dynamically when using the slicer. You might consider creating measures.

For your question, here is the method I provided:

Here's some dummy data

"WO Raw Data"

"BaseSummary"

"Operational"

Create measures.

No. of Soft Repairs = 
var _Type = SELECTEDVALUE('BaseSummary'[MonthYearMachineType])
RETURN
COUNTROWS(
    FILTER(
        'WO Raw Data',
        'WO Raw Data'[MonthYearMachineType] = _Type
        &&
        'WO Raw Data'[HARDSOF] = "Soft"
        &&
        'WO Raw Data'[MTBF Repair?] = "Repair"
    )
)

No. of Hard Repairs = 
var _Type = SELECTEDVALUE('BaseSummary'[MonthYearMachineType])
RETURN
COUNTROWS(
    FILTER(
        'WO Raw Data',
        'WO Raw Data'[MonthYearMachineType] = _Type
        &&
        'WO Raw Data'[HARDSOF] = "Hard"
        &&
        'WO Raw Data'[MTBF Repair?] = "Repair"
    )
)

No. of Repairs = 'BaseSummary'[No. of Hard Repairs] + 'BaseSummary'[No. of Soft Repairs]

MTBF = DIVIDE(SELECTEDVALUE('Operational'[Weeks]), 'BaseSummary'[No. of Repairs])

Here is the result.

Regards,

Nono Chen

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

[CMoppet]

@v-nuoc-msft 

Ah...I've just spotted one small issue.  I've added slicers for 'HARDSOFT' which works as per your efforts.  I've also added a slicer for 'MACHINE TYPE', and this also works great.

But....I need to be able to add a slicer for 'MACHINE BRAND' as well.  Each Machine Brand contains a number of MACHINE TYPES.  This column is present in both of the tables we've been using, and I've linked them (many to many) in the model.  

I created a Moving Average measure too, which does seem to respond to the MACHINE BRAND slicer, but the MTBF doesn't.  Here is the measure for the Moving Average:

Moving Average MTBF = AVERAGEX(FILTER(BaseSummary,[Machine Type]=EARLIER(BaseSummary[Machine Type])&&[Month/Year]<=EARLIER(BaseSummary[Month/Year])),[MTBF])

If I select a specific machine type, it works fine, like this:

But if I want to display the chart at a machine brand level, I just get this:

Please can you help me understand what link is missing?

Thanks so much again

[v-nuoc-msft · Accepted solution]

Hi @CMoppet 

If you are creating a measure, it is first recommended that you do not use EARLIER, which is used in the column. You need to use MAX in measure.

If you still have questions, you can make a new post. You can share your data to get better help. Delete sensitive data.

Regards,

Nono Chen

[CMoppet]

I don't know how to thank you enough for taking the time to explain the approach and attach the example.  It all worked perfectly and I know I'll rely on this logic for future projects too.  Thank you soooooo much 🙂