How to group by measure?

[booooosevic]

Considering a ORDERS table, have customer_id, product_id, order_date columns.

In SQL, if we want to know

how many customers ordered a specified number of different products,

we could write the query like this:

select
	a.product_cnt,
	count( customer_id ) customer_cnt 
from
	( select 
			customer_id, 
			count( distinct product_id ) product_cnt 
		from orders a 
		group by a.customer_id ) a 
group by
	a.product_cnt;

I'm wondering how to achieve this in power bi with DAX.

What I want is a dynamic table, not a static derived static table. Means the output will change when the date filter is applied.

[TomMartens]

Hey,

I guess you're looking for the DAX function COUNTDISTINCT.

Create a measure like so:
COUNTDISTINCT('orders'[product_id])

Hopefully this what you are looking for.

Regards,
Tom

[v-chuncz-msft · Accepted solution]

@booooosevic ,

You may take a look at the following post.

https://community.powerbi.com/t5/Desktop/Create-a-bar-graph-with-measures-as-axis/td-p/455900