Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi all,
I'm having trouble doing this DAX calculated column. So the scenario basically is:
We've got a order table containing the Order name and Order number and a related many to 1 table of products containing the product name and order number.
If the products in the order contains product 1 only or product 1 and product 2 then it's a bundled product. If the product in the order only contains product 2 then it's a single purchase. I've got an attached PBIX file below in which basically I just want to have a column that identifies whether the order is bundled or single.
Any help would be greatly appreciated.
Mike
Solved! Go to Solution.
Hey @Mike282
Here is my solution. PBIX is attached below as well.
Bundle/Single = IF ( CALCULATE ( DISTINCTCOUNT ( 'Products'[Product Name] ), FILTER ( 'Products', 'Products'[Order Number] = EARLIER ( 'Products'[Order Number] ) ) ) = 1 && Products[Product Name] = "Product 2", "Single", "Bundle" )
Summarized Products = SUMMARIZE ( Products, Products[Order Number], Products[Bundle/Single] )
My Bundled? = LOOKUPVALUE ( 'Summarized Products'[Bundle/Single], 'Summarized Products'[Order Number], 'Order'[Order Number] )
Cheers!
A
Hey @Mike282
Here is my solution. PBIX is attached below as well.
Bundle/Single = IF ( CALCULATE ( DISTINCTCOUNT ( 'Products'[Product Name] ), FILTER ( 'Products', 'Products'[Order Number] = EARLIER ( 'Products'[Order Number] ) ) ) = 1 && Products[Product Name] = "Product 2", "Single", "Bundle" )
Summarized Products = SUMMARIZE ( Products, Products[Order Number], Products[Bundle/Single] )
My Bundled? = LOOKUPVALUE ( 'Summarized Products'[Bundle/Single], 'Summarized Products'[Order Number], 'Order'[Order Number] )
Cheers!
A
Just as an update to my question:
The end result is that I want to make sure I can count what are orders of the single product (product 2 only) and what orders are purchased as a bundle (any order with linked products that are grouped by the order number and contain product 1 in the bundle). I though of using a column to flag whether the order is a bundle or single.
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 |
---|---|
93 | |
87 | |
80 | |
69 | |
68 |
User | Count |
---|---|
226 | |
129 | |
119 | |
83 | |
77 |