[EDIT] … PBIX file uploaded
Goals & Metrics EDNA.pbix (312.6 KB)
I need some help refining my ALL function call so it is more targeted about it’s clearing of the filter context.
These are the elements I’m working with
- Month slicer … allows the user to select a month.
- PlacementType Slicer … allows user to select from these PlacementTypes.
- Consultants on Billing … measure that I am creating.
This screenshot is just those elements. The main report has several more measures. I created this just to keep it simple.
The report is a month by month report, hence the month slicer. We also want to filter by PlacementType. Keeping PlacementType is my challenge.
This is the measure I have thus far. It returns the correct number of ‘Consultants on Billing’. However, due to the user of ALL(Placement) it is not going to honor the PlacementType slicer.
Consultants on Billing =
CALCULATE(
[Placements],
FILTER(ALL(Placement),Placement[PlacementStatus] = “Approved”)
)
I’ve attempted a few ways to refine this. This was my last attempt.
Test Consultants on Billing =
CALCULATE(
[Placements],
FILTER(
ALL(Placement[PlacementStatus]),
Placement[PlacementStatus] = “Approved”
),
FILTER(
ALL(Placement[PlacementDate]),
Placement[PlacementDate] <= MAX(‘Date’[Date])
)
)
Basically, I’m trying to get all that are “Approved” while specifically eliminating the date slicer filter context. However, it returns 1 instead of the 208 that it should.
Any insight on using ALL in this situation or a suggestion of a different direction to go?
I can build a stripped down, anonymized version of the PBIX if it will help.