Forum Discussion

GoingIncognito's avatar
GoingIncognito
Icon for Advocate III rankAdvocate III
6 years ago
Solved

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!

Need Help
  • v-yuta-msft's avatar
    v-yuta-msft
    6 years ago

    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.

3 Replies

    • GoingIncognito's avatar
      GoingIncognito
      Icon for Advocate III rankAdvocate III
      6 years ago

      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.

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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.