Sumx, format and crossfilter

[GoingIncognito]

I've a matrix that gets its rows from two tables: a dimension table for instruments traded in stock market and a fact table of of traded stocks. I've no column values in the table, and all the values come from the fact table. Quite simple.

Now, I need to format the numbers in my matrix like this: 999 999 999.99. And so far I had great success using creating new measures that used older measures I had written (before the need to format) like this: Format([measure], "### ### ###.#0"). This worked fine untill now.

I have a simple measure: sumx(filter(fact, fact[date]=max(fact[date])), fact[amount]). But when ever I use format on this measure the matrix shows all the rows. Even the blank ones. So somehow the format with sumx breaks up the crossfiltering between tables? I tried one variation of crossfilter(fact,dimension,both) but that didn't work either. How should I proceed? What sort of a measure should I write?

For the format withouth crossfilter I tried:

1  format(sumx())

2 sumx(filter(),format()).

Thank you!

[v-yuta-msft · Accepted solution]

@GoingIncognito ,

Format the measure using dax like pattern below:

Measure =
IF (
    MAX ( fact[date] ) = CALCULATE ( MAX ( fact[date] ), ALL ( fact ) ),
    FORMAT (
        SUMX ( FILTER ( fact, fact[date] = MAX ( fact[date] ) ), fact[amount] ),
        "### ### ###.#0"
    ),
    BLANK ()
)

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

It should be

format(sumx(filter(),[measure]))

[GoingIncognito]

Thank you Amit for declaring the right syntax!

Finally I solved this with very simple if:

if(sumx()<>0, sumx(), blank())

now I don't get blank rows. But this still doesn't make sense to me, why I'd get blank rows in the first place.