cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Anonymous
Not applicable

## How to leave rows out of calculation when filters applied?

Gday,

I'm relatively new to PBI and a measure I was previously using is now not functioning properly as I've applied a new dimension and filter to the data. The previous measure was:

Weekly_Availability = IF('Measures Table'[Total_Project_Manning] > 1, 0, 1- [Total_Project_Manning])
This measure would return a number between 0 and 1 depending on what availability the relevant category had. I now need to filter by another dimension but when I do this the filtered out rows go to zero and the measure then shows (1 - 0 = 1) whereas I need people to not show on the visual at all if filtered out.

4 REPLIES 4
Community Support

Hi, @Anonymous

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
You should check the forum Featured first:

Best Regards,
Community Support Team _ Janey
Anonymous
Not applicable

Thanks @amitchandak. The issue is that sometimes people are over allocated so I still require the condition if [Total_Project_Manning] > 1, 0. This means that if they are overallocated they have no availability.

My issue is that when the filter of department is applied, it will bring the value to zero for the people not in that department and so it shows every person outside the department on the visual. Whereas I just need to see availability of people within a single department when filtered by slicer.

Super User

@Anonymous , Again force the department filter. Refer to my example of a date where I added +0 and again forced the date filter

0 between range
Measure = var _1= SUM(Opportunity[Opportunity count]) +0
var _min = minx(ALLSELECTED('Calendar'), 'Calendar'[Date])
var _max = maxx(ALLSELECTED('Calendar'), 'Calendar'[Date])
return
CALCULATE(if(max('Calendar'[Date]) <_min || max('Calendar'[Date]) >_max , BLANK(), _1))

You will need like

filter(department, department[Name] in allselected(department[Name] ))

Super User

@Anonymous , what ever you do not want to show, should return blank

example

Weekly_Availability = IF('Measures Table'[Total_Project_Manning] > 1, blank(), 1- [Total_Project_Manning])

Or use this in visual level filter and check for value 1 or 0

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors