Matrix showing wrong rows

[marama]

Hi!

I have a clients / projects / invoices+credits hierarchy arranged in a crossjoin setup and related using a calculated column.
I need to have a measure on crossjoin level to do some DAX calculations (total = invoice - credit).
But the projects row the resulting matrix visual is also showing projects belonging to clients other then shown in client row. I've spent hours trying to figure this out, maybe someone can clarify for the poor novice.

pbix file here:
https://drive.google.com/file/d/1Dc6Hb0O83HwCWSLSmNbIaNscL4egGiJc

Thank you

[Anonymous]

Hi @marama ,

The problem is in the relationship.

Please check:

1. There must be no NULL or Blank values in Table 1 (From Column) of the relationship

2. Every Value in Table 1(From Column) must have corresponding value in Table 2(To Column) of the relationship.

Power BI is not going to throw you an error if referential integrity is screwed in your data-set, instead you will get inconsistencies in results.

So table 'crossjoin_month_projects':

crossjoin_month_projects =
CROSSJOIN (
    SELECTCOLUMNS ( months, "month", [month] ),
    FILTER ( projects, projects[client] <> BLANK () )
)

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[marama]

Thank you, but it's not working. It still shows all the projects. I believe inconsistency comes from the measure (maybe blanks in the measure), not the blanks in the crossjoineed table. If I remove the measure from matrix and include the values, that in works. But if I include the measure in the matrix, then it breaks.

This is my measure:

total = SUM(invoices[amount invoice]) - SUM(credit[amount credit])