Forum Discussion
DAX Measure breaks relationship
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 |
| 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
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
4 Replies
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
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.
Perhaps obvious to you! 🙂
Is this is a flawed approach? Right approach, flawed execution? How would you solve this problem?
