Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
CMoppet
Helper IV
Helper IV

Connecting two tables with Common Fields - Unexpected Result in Visual

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.

CMoppet_5-1725392656885.png

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.

 

 

 

2 ACCEPTED SOLUTIONS
v-yaningy-msft
Community Support
Community Support

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



View solution in original post

CMoppet
Helper IV
Helper IV

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

View solution in original post

3 REPLIES 3
CMoppet
Helper IV
Helper IV

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
Community Support
Community Support

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



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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.