Count number of orders that exist in projects that contains at least 20 orders

MrMano — Mon, 31 Oct 2022 10:58:55 GMT

Hi guys,

I've been trying to solve this issue for some hours but I'm getting nowhere.

Basically, I have two tables, one for ORDER and one for PROJECT. I'm trying to count how many orders on average there are per project. However, I need to filter out projects where the orders are less than 20.

So I've created a measure that only calculates the count of projects where the orders are more than 20:

count project id filter =

CALCULATE(COUNT(PROJECT[PROJECT NUMBER]), FILTER(PROJECT, 'ORDER'[count order id] > 20))

The measure count order id is just a DISTINCTCOUNT on order id, nothing else.

However, I can't seem to create the measure that counts number of orders that exist WITHIN those projects. I feel like the logic is quite circular because I'm trying to filter a table based on conditons on itself. This is what I've tried, but I'm guessing I'm using the wrong logic.

count order id filter =

CALCULATE(COUNT('ORDER'[ORDER_ID]), FILTER('ORDER', ORDER'[count order id] > 20))

This obviously does not work as it does not have any condition on the PROJECTS. I also tried using some AND operator, but I can't figure out where and how to place the condition to only include projects where order count is > 20.

Does anyone have any ideas? I'm guessing I could use a totally different logic here, but I'm not super experienced in DAX.

Thanks!

Re: Count number of orders that exist in projects that contains at least 20 orders

johnt75 — Mon, 31 Oct 2022 11:37:39 GMT

You could get the average number of orders with

Avg orders > 20 = AVERAGEX ( FILTER ( VALUES ( 'Project'[Project number] ), [count order id] > 20 ), [count order id] )

Re: Count number of orders that exist in projects that contains at least 20 orders

MrMano — Mon, 31 Oct 2022 12:11:50 GMT

Thank you! It worked perfectly.

topic Re: Count number of orders that exist in projects that contains at least 20 orders in DAX Commands and Tips

Count number of orders that exist in projects that contains at least 20 orders

Re: Count number of orders that exist in projects that contains at least 20 orders

Re: Count number of orders that exist in projects that contains at least 20 orders