More elegant DAX for grouped counts - understanding the Contoso Data Set (link to executable code)

[AhsenMajid]

Hi all!

I wrote the following query to understand the the granularity of the sales table in the contoso db - you can access it at dax.do, or you can access my code at https://dax.do/nCPqFjYyEYDGbE/ 

Each row in 'Sales' was obviously not an order, so what was it? My thinking is that each row is a different product in a an order, making the granularity of the 'Sales' table Product-Order. 

It turns out this isn't true:

Two questions I'd really like help with:

1. Is there a more elegant way to write the following code? (is it even correct?)
2. What is the granularity of the Sales table?
3. Bonus question after the code

@AlbertoFerrari, @marcorusso 

https://dax.do/nCPqFjYyEYDGbE/

Code below (data, model, and environment at the link above)

-- trying to understand the granularity of the 'Sales' 
-- table. Specifically, is each row in the sales table a 
-- different product in a unique order? And if not, 
-- what is it?

-- I Hypothesize that each row in the sales table represents 
-- one product in an order, i.e. the granularity of the sales 
-- table is sales-order x product-key. The number of unique 
-- product keys per order should equal the number of rows for the 
-- order in the sales table. This code shows they do not. 

DEFINE
MEASURE Sales[Rows In Sales Table] = COUNTROWS( 'Sales' )
MEASURE Sales[Distinct Values of Product Key] = COUNTROWS( DISTINCT ( 'Sales'[ProductKey] ) )

EVALUATE

-- create a table that lists all order numbers, 
-- and counts the number of rows in the sales table
-- for that order number, as well the number of
-- distinct product keys for that order number 
VAR product_keys_distinct = 
		ADDCOLUMNS (
			DISTINCT( Sales[Order Number] ),
			"Rows in sales table", [Rows In Sales Table],
			"PKs in Sales table", [Distinct Values of Product Key]
		)

-- filter the product_keys distinct table to include
-- only those product keys for which the hypothesis is true
VAR pk_count_measures_equal = 
	ADDCOLUMNS(
		FILTER (
			product_keys_distinct,
			[Rows in sales table] == [PKs in Sales table]
		),
		"are PKs equal to sales rows", "yes"
	)
    
-- Summarize to return in a final table
VAR equal_summary_table = 
        ADDCOLUMNS(
			DISTINCT ( 
				SELECTCOLUMNS ( 
					pk_count_measures_equal , 
					"are PKs equal to sales rows2", [are PKs equal to sales rows] 
				) 
			), 
			"number of product keys", COUNTROWS ( pk_count_measures_equal ) 
		)

-- filter the product_keys distinct table to include
-- only those product keys for which the hypothesis is false
VAR pk_count_measures_not_equal = 
	ADDCOLUMNS(
		FILTER (
			product_keys_distinct,
			[Rows in sales table] <> [PKs in Sales table]
		),
		"are PKs equal to sales rows", "no"
	)
     
-- Summarize to return in a final table
VAR not_equal_summary_table =        
        ADDCOLUMNS(
			DISTINCT ( 
				SELECTCOLUMNS ( 
					pk_count_measures_not_equal , 
					"are PKs equal to sales rows2", [are PKs equal to sales rows] 
				) 
			), 
			"number of product keys", COUNTROWS ( pk_count_measures_not_equal ) 
		)

-- Union summary tables
RETURN 
	UNION ( not_equal_summary_table ,
            equal_summary_table
    )

Bonus question:
Unioning the tables pk_count_measures_not_equal and pk_count_measures_equal before summarizing them meant that subsequent attempts to use SUMMARIZECOLUMNS failed abysmally - I could group on yes/no, but the sum was the total number of all product keys, no filter. 

[Anonymous]

Hi @AhsenMajid ,

Have you already solved the problem? If so, can you share your solution here and mark the correct answer as standard to help other members find it faster? Thank you very much for your co-operation!

Best Regards,

Clara Gong

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[AntrikshSharma]

 @AhsenMajid 

COUNTROWS ( pk_count_measures_equal )

This part of the code in ADDCOLUMNS isn't going to work as you expect it to, it will return the total rows of that table variable in each row of ADDCOLUMNS, ADDCOLUMNS only provides a row context but not a filter context so your COUNTROWS can't be filtered here, another issue here is thinking that Variables can be filtered in a new filter context, once defined the value of a variable can't be changed.

[lbendlin]

In the real world you have orders and order line items. It is not uncommon for products in an order being listed multiple times, under different order line item numbers, or inside of configurations (CTO items) or bundles (BTO items).

Oh, and these "guaranteed to be unique" order numbers can sometimes be reused, for different customers or countries.

Contoso is just a toy.