I have two tables with one to many relationship, what matrix shows depends on ....

[Marta777]

I tried Googling but could not find an answer. I have two tables with one 1-to-many relationship (cost centres), what the matrix visual shows depends on whether I filter on cost centre in one table or the other.

I have created two measures M1 and M2, with differences marked in red:

M1 = CALCULATE(

        SUM('Transactions'[Amount]),

        LEFT('Tag'[CostCentre],1)="R",

        'Transactions'[Account]>=3000 && 'Transactions'[Account]<9000,

        'Tag'[Tag]="U",

        Transactions[FY]="22/23"

)

M2 = CALCULATE(

        SUM('Transactions'[Amount]),

        LEFT('Transactions'[CostCentre],1)="R",

        'Transactions'[Account]>=3000 && 'Transactions'[Account]<9000,

        'Tag'[Tag]="U",

        Transactions[FY]="22/23"

)

M1 shows the correct totals

M2 shows A*,P* and R003 cost centres which should be excluded and incorrect totals for R001 and R002.

Q1: Please explain why different results are produced even though my tables have only one 1-to-many relationship?

Q2: How M1 needs to be amended so that it shows the correct totals on the account lines? Currently, it shows as below. Please note that account 9000 should be excluded.

 Thank you!

[some_bih · Accepted solution]

Hi @Marta777 as you analysing results per CostCenter, I would suggest to check data about it. 

Are all cost centers values, present in both tables? So basically check  'Transactions'[CostCentre] vs 'Tag'[CostCentre]

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

[Marta777]

Hi @some_bih , yes, all cost centre values are present in both tables. I only showed an extract from the 'Transactions' table to show how it is structured. The 'Transactions' table has all six cost centres with various accounts and amounts.