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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
davidoz
Frequent Visitor

Adding a value to DAX formula breaks my filter context

I have an absolutely maddening powerbi issue which I am having trouble resolving

 

I have a measure called CB COST PLUS PCT which is trying to calculate the percentage we marked up "CB" expenses. As a starting point, if I define the measure as:

 

CB COST PLUS PCT = [CB REVENUE] / [CB EXPENSE]

 

 

Everything works just fine. Here is an example of my matrix below with the values hidden

before.jpg

 

However, the measure is a percentage, so really the correct measure is

 

 

CB COST PLUS PCT = [CB REVENUE] / [CB EXPENSE] - 1

 

 

 

When I adopt this measure, everything breaks. Because the "-1" makes every date, every job, every account etc... equal to -100%, then now there is a value for every one of those things and I can't make it go away. See example below:

 

after.jpg

 

List of things I have already tried with unsuccessful results:

  • Filtering the page, and visuals to exclude anything where CB P&L is blank or equal to zero
  • Filtering the page, and visuals to exclude anything where CB COST PLUS PCT = -1
  • Making the measure null, or "" if equal to -1
  • Incorporating CALCULATE / FILTER

I have absolutely no idea where to go from here. It is such a simple problem and I need it to work but I can't figure it out

1 ACCEPTED SOLUTION
CoreyP
Solution Sage
Solution Sage

Have you tried something like:

IF( OR( ISBLANK( [CB REVENUE] ) , ISBLANK( [CB EXPENSE] ) ) , BLANK() , [CB REVENUE] / [CB EXPENSE] - 1 )

View solution in original post

1 REPLY 1
CoreyP
Solution Sage
Solution Sage

Have you tried something like:

IF( OR( ISBLANK( [CB REVENUE] ) , ISBLANK( [CB EXPENSE] ) ) , BLANK() , [CB REVENUE] / [CB EXPENSE] - 1 )

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors