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

AhsenMajid — Sun, 13 Oct 2024 05:39:30 GMT

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:

Is there a more elegant way to write the following code? (is it even correct?)
What is the granularity of the Sales table?
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.

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

lbendlin — Sun, 13 Oct 2024 11:29:21 GMT

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.

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

AntrikshSharma — Sun, 13 Oct 2024 15:07:10 GMT

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.

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

Anonymous — Thu, 24 Oct 2024 06:37:24 GMT

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.

topic Re: More elegant DAX for grouped counts - understanding the Contoso Data Set (link to executable code) in DAX Commands and Tips

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

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

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

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