Distribution of one measure by another - pbix and dax.do code provided
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
