cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Filter out Blanks without filtering other measure

I have two quick measures. One sums values that have been billed (Total Billed) to the client per job the other sums values I have been billed by subcontractors (Total Subcontrator Costs) per job. Not every job uses subcontractors so I am trying to filter out by usin gthe filter is not blank but that ends up also filtering the transaction of my Total Billed as well. How can I filter on just my Total Subcontractor Costs?

 

 

Total Sub Contractor Cost = 
CALCULATE(
	SUM('Dim_Job_Ledger_Entry'[Total Cost]),
	'Dim_Job_Ledger_Entry'[Posting Account No.]
		IN { "52200", "52100" }
)

Total Billed = 
CALCULATE(
	SUM('Dim_Job_Ledger_Entry'[Total Billed Price]),
	'Dim_Job_Ledger_Entry'[Posting Account No.]
		IN { "40200", "40300", "40400" }
)

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: @Re: Filter out Blanks without filtering other measure

Hi @CaptainHardhead ,

 

Try to create a measure and apply it to the visual level filter.

Measure = IF(ISBLANK([Total Sub Contractor Cost]),0,1)

test_Filter out Blanks without filtering other measure.PNG

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
Highlighted
Super User II
Super User II

@Re: Filter out Blanks without filtering other measure

HI @CaptainHardhead ,

 

Your requirement is not clear as you have already created measures correctly.

can you share some sample data to understand how you want to use NOT BLANK filtering?

 

Thanks,

Pragati





If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




Highlighted
Frequent Visitor

Re: @Re: Filter out Blanks without filtering other measure

Sorry, @Pragati11 a better way of explaining it would be that I need DAX to ignore filters on the Total Billed measure. I'm trying to use ALL in the Total Billed measure but still filter the 3 accounts I've listed in the the measure.

CaptainHardhead_0-1597087107946.png

With the two measures I currently have I have blank values and I wan to filter those jobs out but when I do it also filters my Total Billed which I don't want to happen.

CaptainHardhead_1-1597087170266.png

 

Highlighted
Microsoft
Microsoft

Re: @Re: Filter out Blanks without filtering other measure

Hi @CaptainHardhead ,

 

Try to create a measure and apply it to the visual level filter.

Measure = IF(ISBLANK([Total Sub Contractor Cost]),0,1)

test_Filter out Blanks without filtering other measure.PNG

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Super User IV
Super User IV

Re: Filter out Blanks without filtering other measure

@CaptainHardhead Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Super User IV
Super User IV

Re: @Re: Filter out Blanks without filtering other measure

@CaptainHardhead  If you want to ignore filters on a measure, use ALLEXCEPT


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors