cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

## Sumx, format and crossfilter

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!

1 ACCEPTED SOLUTION
Community Support

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.

3 REPLIES 3
Community Support

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.

Super User

It should be

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

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors