Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
StanleyBlack
Helper I
Helper I

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
TomMartens
Super User
Super User

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

 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.