powerbidaxpowerbi-desktopmeasuredaxstudio

DAX SUMMARIZECOLUMNS why does FORMAT function cause blank rows from Date table to display

Partying with Contoso while studying DAX with sqlbi.com Testing querys with daxstudio.

EVALUATE
SUMMARIZECOLUMNS(
    'Date'[Year],   
    'Date'[Month],  
    "Sales", Sales Amount]
)

Works with rows displayed and with no blank cells.

EVALUATE
SUMMARIZECOLUMNS(
    'Date'[Year],  
    'Date'[Month], 
    "Sales", FORMAT( [Sales Amount], "$#,0.00" )
)

Works and with those years with no associated data displayed also?
Why does FORMAT effect this way?

enter image description here

Solution

  • From DAX Guide (my bold):

    https://dax.guide/format/

    Return values

    Scalar A single string value.

    A string containing Value formatted as defined by Format string. The result is always a string, even when the value is blank.

    Blanks are being pruned in your first example. In your second example, a string is returned every row.