The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to 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
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.
@zbeg , obviously, data lineage is lost when you use "{ }" to create a new table __ItemsFound.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Perhaps obvious to you! 🙂
Is this is a flawed approach? Right approach, flawed execution? How would you solve this problem?
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
16 | |
15 | |
14 |
User | Count |
---|---|
44 | |
44 | |
41 | |
27 | |
20 |