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

How to remove table row with £0 value

Hi there, 

 

I have a table which is correctly picking up the data that should be shown, however isnt relevant to the current year.

 

i need the table to show only values in the 21/22 year and all £0 rows to be removed. can this be done? if so how?

 

Stuffit25_1-1633439560422.png

 

 

1 ACCEPTED SOLUTION
BA_Pete
Super User
Super User

Hi @Stuffit25 ,

 

Three simple ways to remove zeroes:

 

Power Query:

Select the column(s) in your fact tables that contain your values. Go to the Transform tab > Replace Values.

In the Find box, type 0, in the Replace With box type null.

Note: this will not remove zeros when zero is the result of a sum between values e.g. 100+50-150 = 0.

 

DAX:

In your measures that produce these numbers, you can add an IF statement to return blank if zero, something like this:

 

_blankMeasure =
VAR __yourCalc =
[your calculation or measure here]
RETURN
IF(
  __yourCalc = 0,
  BLANK(),
  __yourCalc
)

 

 

Report GUI:

Select your visual that you want to remove zeroes from. Find the measure in the visual-level filter pane on the right. Set the filter to yourValue > 0.

 

Regarding the current year only requirement, the easiest way would be to create a calendar table (you should always have one), create a column in it that uses whatever logic you need to identify 'current year', relate calendar[date] to factTable[date], then use your calendar current year field in page-level, visual-level, or measure filters accordingly.

 

Pete

View solution in original post

1 REPLY 1
BA_Pete
Super User
Super User

Hi @Stuffit25 ,

 

Three simple ways to remove zeroes:

 

Power Query:

Select the column(s) in your fact tables that contain your values. Go to the Transform tab > Replace Values.

In the Find box, type 0, in the Replace With box type null.

Note: this will not remove zeros when zero is the result of a sum between values e.g. 100+50-150 = 0.

 

DAX:

In your measures that produce these numbers, you can add an IF statement to return blank if zero, something like this:

 

_blankMeasure =
VAR __yourCalc =
[your calculation or measure here]
RETURN
IF(
  __yourCalc = 0,
  BLANK(),
  __yourCalc
)

 

 

Report GUI:

Select your visual that you want to remove zeroes from. Find the measure in the visual-level filter pane on the right. Set the filter to yourValue > 0.

 

Regarding the current year only requirement, the easiest way would be to create a calendar table (you should always have one), create a column in it that uses whatever logic you need to identify 'current year', relate calendar[date] to factTable[date], then use your calendar current year field in page-level, visual-level, or measure filters accordingly.

 

Pete

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors