Connecting two tables with Common Fields - Unexpected Result in Visual

[CMoppet]

Hello,

I've attached a sample of data here:  https://drive.google.com/file/d/1dRICRP8-15D6lOjgwTHvPv_TyLfnQcsW/view?usp=drive_link

I am trying to create a table (below) with an accompanying bar chart so that users can view MTBF data, sliced by Brand and/or Type and/or Age Bracket and/or Market.

The data for this spans two tables, with various columns in common, which I have connected in the model.  I am getting unexpected results when I try and pull through data from the second table, despite having connected the common columns in the model.  There's an example highlighted below with the expected result written on the screenshot.  You can see in the attached 'RepsSummary' table that there are only 104 ABC Element Repairs in Germany in October 2023.

Please can someone help me understand what step I am missing?   If I correct this column, will my existing MTBF measure (seen in screenshot above) then work correctly?  It's important that the MTBF is available on a bar chart (Month/Year on x-axis and MTBF on Y-axis) and that they can select whatever combination of Machine Brand and/or Machine Type and/or Age Bracket and/or Market they want, with the MTBF adjusting accordingly.

Thank you in advance.

[v-yaningy-msft · Accepted solution]

Hi, @CMoppet 

The links you share need access permissions to be accessible, and you can set them to be accessible to anyone so that others can also access the files to help you solve the problem faster.

Best Regards,
Yang
Community Support Team

[CMoppet · Accepted solution]

I have now solved the issue raised in this post.  I figured out how to filter the column, as per below.

Repairs =

VAR MonthYear = [Month/Year]

VAR MachineBrand = [Machine Brand]

VAR MachineType = [Machine Type]

VAR Age = [Age Bracket]

VAR Market = [Market]

VAR _Results = SUMX(FILTER(ALL('RepsSummary'),'RepsSummary'[Age Bracket]=Age && [Machine Brand]=MachineBrand && [Machine Type]=MachineType && [Market]=Market && [Month/Year]=MonthYear),[Total No. of Breakdowns])

RETURN

_Results

and my MTBF is calculated like this

MTBFTest =

VAR MonthYear = [Month/Year]

VAR MachineBrand = [Machine Brand]

VAR MachineType = [Machine Type]

VAR Age = [Age Bracket]

VAR Market = [Market]

VAR _Results = SUMX(FILTER(ALL('RepsSummary'),'RepsSummary'[Age Bracket]=Age && [Machine Brand]=MachineBrand && [Machine Type]=MachineType && [Market]=Market && [Month/Year]=MonthYear),[Repairs])

RETURN

IF([Repairs]>0,DIVIDE([Operational Weeks in Reporting Month],[Repairs]),[Operational Weeks in Reporting Month])

[v-yaningy-msft · Accepted solution]

Hi, @CMoppet 

The links you share need access permissions to be accessible, and you can set them to be accessible to anyone so that others can also access the files to help you solve the problem faster.

Best Regards,
Yang
Community Support Team

[CMoppet]

@v-yaningy-msft  thank you!  Hopefully it's now available for all to see 🙂  Appreciate your help