Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
MrMano
Frequent Visitor

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

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!

 

 

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

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

View solution in original post

2 REPLIES 2
MrMano
Frequent Visitor

Thank you! It worked perfectly.

johnt75
Super User
Super User

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.