Emprty string measure break filtering

[Anonymous]

Hi,

I am having some troubles when a measure return an empty string, for example when I use FORMAT(). In this conditions some values appears although the corresponding column is filtered.

I reproduced this behaviour in the pbix attached.

Brand table is joined to Range table which is joined to Data table. I then display a Sales measure per range and filter on the Brand table in the page. If I used formatted sales measure, ranges that does not belong to the filtered brand still get displayed.

Note that if I filter on the visual using the brand column of the range table, it works..

What is going wrong here ?

Thanks for your help

PBIX FILE

[ChrisMendoza]

@Anonymous -

The documentation, https://docs.microsoft.com/en-us/dax/pre-defined-numeric-formats-for-the-format-function, "If value is BLANK() the function returns an empty string." 

Why not use the Formatting Formats that are built in to display Decimal instead of converting the numbers to Text?

[Anonymous]

I know that FORMAT return empty strings. 
I already use the built in format but I want a different format for one specific column, thus FORMAT() helps me to achieve this.

The issue here is not about formatting numbers but rather understand why empty strings "break" the tables filtering.

[ChrisMendoza · Accepted solution]

@Anonymous -

The behavior is probably documented somewhere. There is plenty of documentation on describing how to deal with empty strings.

If you want the slicer to work as what I believe you intended you could use:

Formatted Sales = 
IF(
    ISBLANK([Sales]),
    BLANK(),
    FORMAT([Sales],"Fixed")
)