Measure breaks join

[RobertSlattery]

I have two very simple tables that I am using to check this issue: a table of orders with value and sign (credit or debit) and a table of invoices with the ref order number and the sale value

 

I join them on order number

I created a Table visual with Order number from the Order Table and IORDNO from the Invoice table and sum the value in the Order Table.

Then I filter to exclude entries where the order number from the order table is blank (i.e. invoices that do not match any of the orders).

Which behaves as expected.

Then I create a measure: Measure = SUMX(orders,[Value] * [Sign])

and add this as a column in the table and the relationships is broken.

How can I have non matching order numbers in the Table when there is a join on this field and why does the measure cause this? 

[DAX0110 · Accepted solution]

Hi @RobertSlattery, if you change the "cross filter direction" from Single to Both, it should fix the problem.

[RobertSlattery]

Thanks, I thought I tried that permutation, apparently not.

Admittedly, I had the direction running from invoices to orders (the columns were running from order to invoice), but even if I reverse the direction, the same behaviour persists.

 

Why?  Why does it behave differently if I include a Measure?

What is the logic behind this behaviour?  It doesn't make any sense to me...

[DAX0110]

Hi @RobertSlattery, this is one of those meaning-of-life things isn't it?  I thought I understood it but everytime I come across this problem I have to spend some time thinking about it.

If you want to delve into the real reason, you can begin with this blog post :

https://powerpivotpro.com/2017/08/autoexist-cross-table-filtering/

[RobertSlattery]

Thanks, that helped but, I don't think it's an existential question.

The reason you have to think about it every time it comes up is because its unexpected behaviour.  This is great fun for techs who love the process of gaining insights into the arcane, but stressful time-wasting for people who just want to be productive.  In short, it's either bad design or a legacy issue that has painted the current designer into a corner.

I see people posting admiration for the gurus who can unravel this mess and wonderment at the intellect and knowledge of Jeffery Wang.  My take on it is that the arcane insights from the gurus only exist because Wang has failed.  At least I have not seen a single use case for this unintuitive behaviour so, I consider it to be, at best a legacy constraint and at worst a bug.

The short answer for me is that, in DAX, a join is not a join.  It doesn't filter as one would expect.  Why? I don't know.

The cube in DAX includes all rows from all related tables in every permutation, regardless of relationships.

It builds a cube by including all distinct values of all of the joined columns and then fills in the other columns on all related tables, according to the relationships. 

So if you have an order with no invoices, there will be a row for every invoice, containing this order number and each invoice key in turn but, all of the invoice fields will be blank and these rows will be hidden by the visual.

If you have an order with two invoices for example, two of the rows will be filled in with the invoice data.

The engine hides rows that are blank so, normally, we are unaware of this.

When you add a measure, even though you associate it with a table, it acts as if it is in the related (in this case invoice) table and the value is calculated for all permutations.  The row hiding algorithm then sees these as non-empty rows and displays them all.

To me, the unintuitive bit is, if the measure has a "home table", why consider it to be associated with all tables?  Why not leverage the home table concept to make the behaviour intuitive?