cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
StanleyBlack Frequent Visitor
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
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
StanleyBlack Frequent Visitor
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.

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

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

Top Ideas
Users Online
Currently online: 207 members 1,754 guests
Please welcome our newest community members: