cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Equivalent to COUNTIFS

Hi,

Apologies, there has been a few topics created on this but I cannot find an answer that applies to my data set.

I'm looking to create a measure that shows the count of a specific spend type, but only for employees over a certain threshold. I want to create a count of the "Hotel" trips, but only for Employees who have done over 10 "Hotel" trips this year. Below is what I currently have, but this brings back a blank result.

``````Number of Business Trips = CALCULATE(COUNT(Concur[Expense Type]),
FILTER(Concur,Concur[Expense Type]="Hotel"
&& CALCULATE(COUNT(Concur[Employee]),Concur[Expense Type]="Hotel")>10))``````

I can create the count of Hotel spends, the problem arises when limiting it to only employees with >10 Hotel trips.

What am I doing wrong here?

Many thanks,

Ollie

4 REPLIES 4
Super User

I would suggest writing your DAX something like below: (keep in mind I am just writing it seeing your DAX. Can't test it)

``````Number of Business Trips =
Var hotelTrips =
CALCULATE(
COUNT(Concur[Employee]),Concur[Expense Type]="Hotel")
)
RETURN
CALCULATE(
COUNT(Concur[Expense Type]),
FILTER(
Concur,
Concur[Expense Type]="Hotel" && hotelTrips>10
)
)``````

See if this works.

Best Regards,

Pragati Jain

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Proud to be a Super User!!

Frequent Visitor

I have tried this also. Where 'Hotel Stays' is:

``Hotel Stays = CALCULATE(COUNT(Concur[Employee]),Concur[Expense Type]="Hotel")``

And No. of Business trips being:

``````Number of Business Trips = CALCULATE(COUNT(Concur[Employee]),
FILTER(Concur,Concur[Expense Type]="Hotel"
&& [Hotel Stays]>10))``````

This also gives a blank result.

Many thanks,

Super User

I am not sure why you are getting blank result here.

Please share some sample data, so that I can test the DAX expression at my end.

Best Regards,

Pragati Jain

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Proud to be a Super User!!

Super User

can you share some data , to see how it´s look.

BR

Bruno

Best regards

Bruno Costa | Super User

Take a look at the blog: PBI Portugal

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors