Measure triggering a full outer join

[Greg_Deckler]

Have been working with a manufacturer and came across something a bit unexpected in DAX and was wondering what the community might think of it. Kind of surprised that I've never came across this before or really seen any threads on it. I've been able to replicate the issue in a sample PBIX file (below) but thought that I would also describe it here.

OTIF.pbix

https://1drv.ms/u/s!ApodhCa32_44o79o0gPmy0Y65Fc4ng

OK, so the setup for this is that I have a fact table like so:

OrderLines

plant_nbr order_nbr line_nbr request_date request_qty ship_date ship_qty #c_OnTime #c_OnTimeQty

1	1	A	1/1/2018	10	1/1/2018	10	1	10
1	1	B	1/1/2018	5	1/2/2018	5	0	0
1	1	C	1/1/2018	15	1/1/2018	15	1	15
2	1	D	1/1/2018	7	1/1/2018	7	1	7
2	1	E	1/1/2018	14	1/4/2018	14	0	0

Basically, customers have orders which consist of multiple lines that are produced by potentially multiple plans. The customer has a specific date (request_date) and quantity (request_qty) that they want the products ordered. Also included in the data is when the order line actually shipped and in what quantity.

Two calculated columsn exist:

#c_OnTime = IF([request_date]>=[ship_date],1,0)
#c_OnTimeQty = IF([#c_OnTime],[request_qty],0)

The first is either a 1 if the order line shipped on time and the second is that if the order shipped on time, what was the quantity that shipped on time, otherwise it is 0 (nothing shipped on time).

I also have a dimension table for the plants that provides more descritive information about the plants:

Plants

plant_nbr plant_desc

1	Plant1
2	Plant2
3	Plant3
4	Plant4

There is the obvious relationship between the two tables on the plant_nbr columns.

Now, I have some measures built around these that I will explain below:

#m_LF = AVERAGE(OrderLines[#c_OnTime])

#m_UF = DIVIDE(SUM(OrderLines[#c_OnTimeQty]),SUM([request_qty]))

#m_OrderOTIF = 
VAR OnTimeLines = CALCULATE(COUNTROWS(OrderLines),FILTER(OrderLines,[#c_OnTime]=1 && [#c_OnTimeQty]=[request_qty]))
VAR TotalLines = COUNTROWS(OrderLines)
RETURN IF(OnTimeLines=TotalLines,1,0)

#m_OTIF = 
VAR OrderTable = SUMMARIZE(OrderLines,OrderLines[order_nbr],"OTIF",[#m_OrderOTIF])
RETURN AVERAGEX(OrderTable,[OTIF])

So the first, #m_LF is the line fill measure that allows the company to roll-up the % of lines filled by order, customer, etc. The second, #m_UF is the unit fill measure that does the same for the % of units shipped on time. The next measure, #m_OrderOTIF is an "on time in full" measure that specifies whether an order was shipped on time. Essentially, if the number of lines shipped on time and in the full amount is equal to the total number of lines, then the order shipped on time (1). Otherwise it did not (0). The last measure, #m_OTIF is the OTIF measure designed to be rolled-up by customer.

So, here is the interesting part. I can create a table with my 5 order line rows in it along with the "plant_desc" column from my Plants table and put the first 2 measures and the last measure in the table visualization and it all works just fine:

However, if I place the #m_OrderOTIF measure in the table (nevermind that that measure isn't really intended to be used at that level) you get the following:

So, I wasn't really expecting that measure to trigger what appears to be a full outer join of the two tables. Was wondering what the community thought about that. Expected/unexpected? Weird/not weird? Any suggestions on how to do it differently where that does not occur?

And, the other odd part is that it doesn't matter if my relationship is single direction or both directions. At first, I figured switching the relationship to Both would fix it, but no dice.

Really just kind of curious about people's thoughts here as I have been working with these technologies for several years now and this is the first time I have seen this behavior. I get that the relationships really represent joins between tables but this one got me scratching my head.

Would love some feedback.

[Phil_Seamark]

Hey @Greg_Deckler

The [#m_OrderOTIF] calculated measure is returning a value for each row based on the filter context of the pivot table.  In this case your filter context is being driven from the all the non-measure columns in the grid wuch as  order_nbr, line_nbr, request_date etc.

The code in your measure appears to always returns a value for each execution of the measure and as if you had hardcoded the measure to return a fixed value such as this.  Setting the "show items with no data" on each of the non-measure columns would produce the same number of rows which would equal the total number of permutations of the distinct values from the non-meaure (implicit AND explicit measures).

#m_OrderOTIF = 1

In this case, your measure will be (logically) executed for every permutation of non-measure column values.  If it returns a non-blank value, it will create a row.

If you want fewer rows, you need to remove non-measure columns or write your DAX so in some circumstances it returns a BLANK().

[Greg_Deckler]

@Phil_Seamark- Yeah, I get why it is doing what it is doing but I'm just surprised I've never run into this before. And I'm not sure how I would get that calculation to return BLANK for some rows. Probably has to do with an if statement but I can't seem to wrap my head around exactly what it would be. Of course, I haven't really sat down and tried very hard, too many other things going on!!

[Greg_Deckler]

@Seth_C_Bauer, @Phil_Seamark, any thoughts on this?