Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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.
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |