More than 3 years have passed since last update.

DAX workout - FORMAT function

PowerBI

Last updated at 2022-04-11Posted at 2022-04-09

モデルの culture はあとから変更したりできなけれどもね、すべてのモデルがいつも一緒と限らない。

2021-09 のアップデートで locale_name 引数の追加

それまでは、モデルの culture に依存していたのだけど、FORMAT 関数で明示的な指定が可能
Azure Analysis Service でも追加になってるはず。

The format strings supported as an argument to the DAX FORMAT function are based on the format strings used by Visual Basic (OLE Automation), not on the format strings used by the .NET Framework. Therefore, you might get unexpected results or an error if the argument doesn't match any defined format strings. For example, "p" as an abbreviation for "Percent" isn't supported. Strings that you provide as an argument to the FORMAT function that aren't included in the list of predefined format strings are handled as part of a custom format string, or as a string literal.

書式文字列は VB と変わらんよということ。Power Query の書式文字列は .NET Framework。

DAX Query (特徴的なものだけ)

DEFINE
VAR D = dt"2022-04-09 16:11:55"
VAR N = 12345.67
EVALUATE
{
    ( "Currency",    FORMAT ( N, "Currency",    "ja-JP" ) ),
    ( "aaa",         FORMAT ( D, "aaa",         "ja-JP" ) ),
    ( "aaaa",        FORMAT ( D, "aaaa",        "ja-JP" ) ),
    ( "dddddd",      FORMAT ( D, "dddddd",      "ja-JP" ) ),
    ( "Long Date",   FORMAT ( D, "Long Date",   "ja-JP" ) ),
    ( "Short Date",  FORMAT ( D, "Short Date",  "ja-JP" ) ),
    ( "Long Time",   FORMAT ( D, "Long Time",   "ja-JP" ) ),
    ( "Medium Time", FORMAT ( D, "Medium Time", "ja-JP" ) ),
    ( "Era",         FORMAT ( D, "ge",          "ja-JP" ) ),
    ( "Era",         FORMAT ( D, "gge",         "ja-JP" ) ),
    ( "Era",         FORMAT ( D, "ggge",        "ja-JP" ) ),
    ( "Era",         FORMAT ( D, "gggee",       "ja-JP" ) )
}

Value1	Value2
Currency	¥12,346
aaa	土
aaaa	土曜日
dddddd	2022年4月9日
Long Date	2022年4月9日
Short Date	2022/04/09
Long Time	16:11:55
Medium Time	04:11 午後
Era	R4
Era	令4
Era	令和4
Era	令和04

DAX Query (丸め)

DEFINE
    VAR N1 = 1001.4
    VAR N2 = 1001.5
    VAR N3 = 1002.4
    VAR N4 = 1002.5

EVALUATE
{
    ( "CurrencyN1", FORMAT ( N1, "Currency", "ja-JP" ) ),
    ( "CurrencyN2", FORMAT ( N2, "Currency", "ja-JP" ) ),
    ( "CurrencyN3", FORMAT ( N3, "Currency", "ja-JP" ) ),
    ( "CurrencyN4", FORMAT ( N4, "Currency", "ja-JP" ) )
}

Value1	Value2
CurrencyN1	¥1,001
CurrencyN2	¥1,002
CurrencyN3	¥1,002
CurrencyN4	¥1,003

その他

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up