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 ty...
Need Help
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,

  • onurbmiguel_'s avatar
    onurbmiguel_
    Power Participant
    4 years ago

    Hello OEade

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

    BR 

    Bruno 

  • Pragati11's avatar
    Pragati11
    Super User
    4 years ago

    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.