DAX Measure breaks relationship

[zbeg]

Hello Power BI community,

I have two tables for my pizza business. Customers submit an order in the PizzaOrderForm table. I have a second table, Pizza, which uses a measure to see what's missing.

Pizza:

OrderID	Crust	Sauce	Cheese	Topping
100	Thin	Red	Mozzarella	Mushrooms
101	Flatbread	White	Mozzarella	Pepperoni
102	Thin		Havarti
103		Red	Mozzarella	Cheese
104	Deep dish	Red	Fontina

PizzaOrderForm:

Form_OrderID	Form_Crust	Form_Sauce	Form_Cheese	Form_Toppping
100	Thin	Red	Mozzarella	Mushrooms
101	Flatbread	White	Mozzarella	Pepperoni
102	Thin	Blue	Havarti	Anchovies
103	Stuffed	Red	Mozzarella	Cheese
104	Deep dish	Red	Fontina	Peppers

I am using the following measure to look at the table Pizza to see what I'm missing:

Still Needs = 
VAR __ItemsFound = 
 {
 ("Crust",MAX(Pizza[Crust])),
 ("Sauce",MAX(Pizza[Sauce])),
 ("Cheese",MAX(Pizza[Cheese])),
 ("Topping",MAX(Pizza[Topping]))
 }

VAR __ItemsNeeded = 
CONCATENATEX(
 __ItemsFound,

 IF( 
 [Value2] = BLANK(),
 [Value1] & "," 
 ),BLANK()
)

VAR __LENGTH = LEN(__ItemsNeeded)-1

RETURN

IF(
__LENGTH <> -1 ,
LEFT(__ItemsNeeded, __LENGTH)
)

If I create a table visual with Form_OrderID and OrderID (the related field), it looks good:

OrderID	FormOrderID
100	100
101	101
102	102
103	103
104	104

 But if I add the measure from above, I get this unexpected result:

Form_OrderID	OrderID	Still Needs
100	101	Crust,Sauce,Cheese,Topping
100	102	Crust,Sauce,Cheese,Topping
100	103	Crust,Sauce,Cheese,Topping
100	104	Crust,Sauce,Cheese,Topping
101	100	Crust,Sauce,Cheese,Topping
101	102	Crust,Sauce,Cheese,Topping
101	103	Crust,Sauce,Cheese,Topping
101	104	Crust,Sauce,Cheese,Topping
102	100	Crust,Sauce,Cheese,Topping
102	101	Crust,Sauce,Cheese,Topping
102	102	Sauce,Topping
102	103	Crust,Sauce,Cheese,Topping
102	104	Crust,Sauce,Cheese,Topping
103	100	Crust,Sauce,Cheese,Topping
103	101	Crust,Sauce,Cheese,Topping
103	102	Crust,Sauce,Cheese,Topping
103	103	Crust
103	104	Crust,Sauce,Cheese,Topping
104	100	Crust,Sauce,Cheese,Topping
104	101	Crust,Sauce,Cheese,Topping
104	102	Crust,Sauce,Cheese,Topping
104	103	Crust,Sauce,Cheese,Topping
104	104	Topping

I'm not sure what's happening here. I've tried changing the relationship (Power BI detected a bidrectional one-to-one relationship) but to no avail.

Can you help me understand what's happening here? This feels like one of those "DAX requires a different way of thinking" conceptual things, but I don't actually know why things are behaving the way they are.

Thank you so much,

-Zaiem

[wdx223_Daniel · Accepted solution]

firstly unpiovt your tables as this

and this

then create a measure like this

Still Needs = IF(SELECTEDVALUE(PizzaOrderForm[Form_OrderID]),CONCATENATEX(FILTER('PizzaOrderForm','PizzaOrderForm'[Form_OrderID] IN VALUES(Pizza[OrderID])&&NOT('PizzaOrderForm'[Attributes] IN VALUES('Pizza'[Attributes]))),'PizzaOrderForm'[Items],","))

then create a table visual and put the Form_OrderID, OrderID and the measure in the values area, like this

[zbeg]

@wdx223_Daniel thank you so much! That worked, and now I'm able to break it apart and see what I was doing wrong. I appreciate this a lot. Thank you for taking the time to respond.

[CNENFRNL]

@zbeg , obviously, data lineage is lost when you use "{ }" to create a new table __ItemsFound.

[zbeg]

Perhaps obvious to you! 🙂

Is this is a flawed approach? Right approach, flawed execution? How would you solve this problem?