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

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

Reply
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.
 
Thanks in advance.
 
4 REPLIES 4
v-janeyg-msft
Community Support
Community Support

Hi, @Anonymous 

 

Did you take amit's advice?

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
If not, please upload some insensitive data samples and expected output.
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.

@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] ))

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Fabric Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.