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

[MrMano]

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!

[MrMano]

Thank you! It worked perfectly.

[johnt75 · Accepted solution]

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]
)