Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
Hi @Anonymous ,
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
Proud to be a Datanaut!
Hi @Anonymous ,
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
Proud to be a Datanaut!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |