How to Distinguish a Bundled Order from an Single Order from a related table containing the product

[Mike282]

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.

Download PBIX file here.

Any help would be greatly appreciated.

Mike

[Anonymous · Accepted solution]

Hey @Mike282 

Here is my solution. PBIX is attached below as well.

1.  Create an index column on products table. In case there are duplicate records, I wanted to show them in a table.
2. Calculate on the products table if order is bundle/single. Use the following DAX:
   

   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"
   )

3. Create a summarized table. Get the unique order numbers from the product table. DAX:
   

   Summarized Products =
   SUMMARIZE ( Products, Products[Order Number], Products[Bundle/Single] )

4. Use a lookup column against the new summarized table above. Determine if single/bundle. DAX:
   

   My Bundled? =
   LOOKUPVALUE (
       'Summarized Products'[Bundle/Single],
       'Summarized Products'[Order Number], 'Order'[Order Number]
   )
   
   

5. That's about all.
6. PBIX FIle
7. Screenshot
   

Cheers!
A

[Mike282]

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.