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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
lagyimeh
Frequent Visitor

Format zero values hypen "-"

I have the below measure, and it returns different figures based on a selected year, if the selected year value is zero(0), i want the zero(0) to be a hypen(-) instead of (0)

 

Amount on school kits = CALCULATE(SUM('master_indicators'[indicator_value]),'master_indicators'[indicator_key]="amt_sch_refurb")

 

Many thanks for the help.

1 ACCEPTED SOLUTION
daXtreme
Solution Sage
Solution Sage

To get something like this:

daXtreme_0-1661067174240.png

you launch the Tabular Editor and change the format of the measure (then save the changes to the model):

daXtreme_1-1661067322722.png

But for this to work, your measure must return 0, not BLANK, since BLANKS can't be formatted - they stand for the absence of a value. You should not format your measure with the FORMAT function because measures should always return the raw figure. If you do this, though, such a measure will not be able to be used by other measures since the value will not be a numeber - it'll be a string. Of course, you can turn a string into a number... but your code will become brittle, difficult to read and much slower.

 

By the way, when you launch Tabular Editor, just connect to the model in the file to be able to manipulate the structure. Tabular Editor can be found on GitHub (and there's a free version that will do in this case). The creator of this tool is Daniel Otykier.

 

Now it's up to you.

View solution in original post

1 REPLY 1
daXtreme
Solution Sage
Solution Sage

To get something like this:

daXtreme_0-1661067174240.png

you launch the Tabular Editor and change the format of the measure (then save the changes to the model):

daXtreme_1-1661067322722.png

But for this to work, your measure must return 0, not BLANK, since BLANKS can't be formatted - they stand for the absence of a value. You should not format your measure with the FORMAT function because measures should always return the raw figure. If you do this, though, such a measure will not be able to be used by other measures since the value will not be a numeber - it'll be a string. Of course, you can turn a string into a number... but your code will become brittle, difficult to read and much slower.

 

By the way, when you launch Tabular Editor, just connect to the model in the file to be able to manipulate the structure. Tabular Editor can be found on GitHub (and there's a free version that will do in this case). The creator of this tool is Daniel Otykier.

 

Now it's up to you.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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