DAX: custom numeric format preventing table filter from working, is this by design?

[tjgamble]

Hello, I'm looking to understand if the behaviour I'm seeing with the "format" command in DAX with respect to table visual filtering is expected and if so is there a workaround?  What I'm trying to do is use the format command to display a negative value in brackets and replace zeros with a "-", like so:

MeasureWithFormat = format(sum('Office Fact'[Count]),"#,###;(#,###);-")

This works until I apply a slicer/filter and the non-selected rows are not removed from the table or matrix visual control in the report.

Here is my full data set where we can see the formatting command working as expected.  No slicers have been applied at this point.  The value: "MeasureWithoutFormat" is just a simple SUM command:   MeasureWithoutFormat = sum('Office Fact'[Count])

When I apply the slicer, rather than the non-selected rows being removed the formatted values just disappear leaving blank rows. The second table shows the effect of the slicer on the table using just the non-formatted measure, which behaves as expected.

Is this expected?  I was expecting that the format command would enable the table to be filtered as normal and the non-selected rows removed rather than the values just hidden.

Thanks in advance.

[FrankAT · Accepted solution]

Hi,

your measure MeasureWithFormat returns in every situation a string and this is never blank.

Use the properties settings inside the data model view. Select your measure and type your custom formatting as you would do in Excel:

Regards Frank AT

[tjgamble]

Thank you, that works fine.