cancel
Showing results for
Did you mean:
Frequent Visitor

## Do not allow a measure element to be filtered

Afternoon everyone,

I have a load of tiered measures and have run into a problem. I use this to calculate the number of students below 90%

```Persistent Absence =
VAR __ISINSCOPE = ISINSCOPE ( Attendance[Year] )
VAR __PACore = CALCULATE(DISTINCTCOUNT(Attendance[UPN-SUID]),FILTER(
VALUES('Attendance'[UPN-SUID]),
[Attendance Percentage YTD] <= 0.90))

RETURN

IF (
__ISINSCOPE,
__PACore,
CALCULATE(__PACore,NOT('Attendance'[Year] IN {"Nursery", "Reception"}
)))```

I then use the following to find the percentage:

`Persistent Absence % = Divide([Persistent Absence YTD], DISTINCTCOUNT(Attendance[UPN-SUID]))`

I then have multiple measures that give me breakdowns of the percentage such as:

```Male Persistent Absence % YTD =
Calculate([Persistent Absence % YTD], 'Student Database (New)'[Gender] IN {"Male"})```

The problem is that I only want to filter the numerator of the second measure by the 3rd and not the denominator. Is there a way to make:

Persistent Absence % = Divide([Persistent Absence YTD], DISTINCTCOUNT(Attendance[UPN-SUID]))

the element in bold not filterable by the 'Student Database (New)' table?

The alternative is editing multiple measures rather than just one.

Thanks,

Stan

2 REPLIES 2
Super User

## Re: Do not allow a measure element to be filtered

Hey,

not if I understand your question correctly, but why don't you just rewrite this

`Persistent Absence % = Divide([Persistent Absence YTD], DISTINCTCOUNT(Attendance[UPN-SUID]))`

to this

`Persistent Absence % = Divide([Persistent Absence YTD], CALCULATE(DISTINCTCOUNT(Attendance[UPN-SUID]) , ALL('Student Database (New)')))`

Regards,
Tom

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Highlighted
Frequent Visitor

## Re: Do not allow a measure element to be filtered

Hi Tom,

Sorry if that was less than clear.

Say I have 1000 kids in a school and 100 are below 90%.

The second calculation would be 100/1000 and = 10%

When I add a filter on the third measure it changes everything so for males say I had 50 below 90% and had 500 males in the school the third measure currently returns:

50/500 when it should return 50/1000 which is the total school population rather than the subgroup total.

Using the measure you suggested seems to make everything way off.

Announcements

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

#### Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

#### PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 207 members 1,754 guests
Recent signins: