The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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!
Solved! Go to Solution.
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.
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.
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the August 2024 Power BI update to learn about new features.
User | Count |
---|---|
121 | |
86 | |
77 | |
55 | |
48 |
User | Count |
---|---|
136 | |
128 | |
78 | |
64 | |
63 |