Skip to main content
cancel
Showing results for 
Search instead 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

Reply
GoingIncognito
Advocate III
Advocate III

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
v-yuta-msft
Community Support
Community Support

@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.

View solution in original post

3 REPLIES 3
v-yuta-msft
Community Support
Community Support

@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
Super User
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.

Helpful resources

Announcements
Europe Fabric Conference

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.

AugPowerBI_Carousel

Power BI Monthly Update - August 2024

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

August Carousel

Fabric Community Update - August 2024

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