Forum Discussion

CMoppet's avatar
CMoppet
Icon for Helper IV rankHelper IV
2 years ago
Solved

Unable to Relate Tables in the Right Way

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

     

  • Anonymous's avatar
    Anonymous
    2 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
CMoppet
Icon for Helper IV rankHelper IV
2 years ago

Anonymous 

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

 
 
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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