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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
OEade
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
Pragati11
Super User
Super User

Hi @OEade ,

 

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


MVP logo


LinkedIn | Twitter | Blog YouTube 

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

Appreciate your Kudos!!

Proud to be a Super User!!

Hi @Pragati11 

 

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,

HI @OEade ,

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


MVP logo


LinkedIn | Twitter | Blog YouTube 

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

Appreciate your Kudos!!

Proud to be a Super User!!

Hello OEade

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

BR 

Bruno 

 


Best regards


Bruno Costa | Super User


 


Did I help you to answer your question? Accepted my post as a solution! Appreciate your Kudos!! 


Take a look at the blog: PBI Portugal 


 


Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.