Distribution of one measure by another - pbix and dax.do code provided

AhsenMajid — Sun, 13 Oct 2024 06:13:42 GMT

I am using the Contoso db (dax.do).

There is a sales table, which is a fact table.

There are also customer, product, and order dimension tables.

I want to see the distribution of the count of distinct product keys by order number. Do most orders have just one product key? 5? etc. (sharing PBIX https://we.tl/t-ynvzHDkXIv and code at https://dax.do/yFMaeAbs5BRRWr/ - data is slightly different, problem and result is the same)

This is the solution in SQL (copied the DB over using DAX Studio).

This is what I get with DAX:

You can see the intermediate table is actually fine, and the number of orders with 7 distinct product keys is 45 (see row num on tabled, ordered dec.). This is exactly the same as what SQL give us.

Question:

How can I get the "no of sales orders" column correct, given I have the intermediate table correct?
Once I've got the the correct result in a table expression, how do I get one in a visual?

Thanks!

DEFINE MEASURE 'Sales'[Distinct Product Key Count] = DISTINCTCOUNT( 'Sales'[ProductKey] ) EVALUATE VAR __tbl_count_product_keys_by_order_number = ADDCOLUMNS ( DISTINCT ( 'Sales'[Order Number] ), "@Distinct Product Keys", [Distinct Product Key Count] ) VAR __tbl_distinct_column_number_of_product_keys = DISTINCT ( SELECTCOLUMNS ( __tbl_count_product_keys_by_order_number, "@No of Distinct Product Keys", [@Distinct Product Keys] ) ) VAR result = ADDCOLUMNS( SUMMARIZE( __tbl_count_product_keys_by_order_number, [@Distinct Product Keys] ), "no of sales orders", CALCULATE( COUNTROWS( __tbl_distinct_column_number_of_product_keys ) ) ) // RETURN result ORDER BY [@Distinct Product Keys] RETURN __tbl_count_product_keys_by_order_number ORDER BY [@Distinct Product Keys] DESC

Re: Distribution of one measure by another - pbix and dax.do code provided

ThxAlot — Sun, 13 Oct 2024 18:01:31 GMT

You're looking to calculate distribution of _count_product_keys_by_order_number, that's to say, _count_product_keys_by_order_number is a dimension; therefore it's no long a calculation on the fly, but a tangible (calculated) column.

First create a calculated table in table view as what you did in __tbl_count_product_keys_by_order_number; then put necessary column in a viz.

topic Distribution of one measure by another - pbix and dax.do code provided in DAX Commands and Tips

Distribution of one measure by another - pbix and dax.do code provided

Re: Distribution of one measure by another - pbix and dax.do code provided