Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Dear all,
I'm currently facing an issue of creating dynamic format strings for Chinese audiences.
We often use a symbol to represent 10K instead of only using thousand and million.
Currently I'm trying to use the dynamic format to fufill my requirement, if the number is lower than 1 million(let's say 51,000), I would prefer the number to show 5.1 (10K),The following code is what I've tried in FORMAT:
SWITCH(
TRUE(),
SELECTEDMEASURE() < 1000000, FORMAT(SELECTEDMEASURE() / 10000, "#,0.0 (10k)",
"$#,##0,,.0 M"
)
The outcome returns 51 (10k) Instead of 5.1 (10K), which part am I doing wrong?
Any help is appreciated,
Thanks!
Solved! Go to Solution.
Hi @alexyin1053
Since the (10k) format is produced by constructing the formatted value itself, it must be enclosed in double-quotes.
Try this:
VAR DoubleQuoteChar = """"
RETURN
SWITCH (
TRUE ( ),
SELECTEDMEASURE ( ) < 1000000,
DoubleQuoteChar & FORMAT ( SELECTEDMEASURE ( ) / 10000, "#,0.0" ) & " (10k)" & DoubleQuoteChar,
"$#,##0,,.0 M"
)
Regards
Hi, @alexyin1053
You can try @OwenAuger method, if it does not work, you can refer to the following way.
SWITCH(
TRUE(),
SELECTEDMEASURE() < 1000000, FORMAT(ROUND(SELECTEDMEASURE() / 10000, 1), "#,0.0 (10k)"),
"$#,##0,,.0 M"
)
Best Regards,
Yang
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
Hi, @alexyin1053
You can try @OwenAuger method, if it does not work, you can refer to the following way.
SWITCH(
TRUE(),
SELECTEDMEASURE() < 1000000, FORMAT(ROUND(SELECTEDMEASURE() / 10000, 1), "#,0.0 (10k)"),
"$#,##0,,.0 M"
)
Best Regards,
Yang
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
Hi @alexyin1053
Since the (10k) format is produced by constructing the formatted value itself, it must be enclosed in double-quotes.
Try this:
VAR DoubleQuoteChar = """"
RETURN
SWITCH (
TRUE ( ),
SELECTEDMEASURE ( ) < 1000000,
DoubleQuoteChar & FORMAT ( SELECTEDMEASURE ( ) / 10000, "#,0.0" ) & " (10k)" & DoubleQuoteChar,
"$#,##0,,.0 M"
)
Regards
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
69 | |
35 | |
21 | |
18 | |
15 |
User | Count |
---|---|
127 | |
31 | |
27 | |
24 | |
22 |