Forum Discussion

OEade's avatar
OEade
Frequent Visitor
4 years ago

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

Need Help

4 Replies

  • Pragati11's avatar
    Pragati11
    Super User
    4 years ago

    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.

     

    • OEade's avatar
      OEade
      Frequent Visitor
      4 years ago

      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,