TOPのみ表示
Top =
CONCATENATE(
CONCATENATEX(
TOPN(
1,
VALUES('data'),
'data'[order by 1],DESC,
'data'[order by 2],ASC
),
'data'[target],
", ",
'data'[order by 3],
DESC
),
""
)
上位N個を連結して表示
Sources =
VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT('data'[target])
VAR __MAX_VALUES_TO_SHOW = 5
RETURN
IF(
__DISTINCT_VALUES_COUNT > __MAX_VALUES_TO_SHOW,
CONCATENATE(
CONCATENATEX(
DISTINCT(SELECTCOLUMNS(TOPN(
__MAX_VALUES_TO_SHOW,data,data[sort by this column],DESC),"target",data[target])),
[target],
", ",
[target],
DESC
),
", など"
),
CONCATENATEX(
DISTINCT(SELECTCOLUMNS(data,"target",data[target])),
[target],
", ",
[target],
DESC
)
)
コードの見方
[column]
DESC/ASCは任意に選択
dataは任意のテーブルに置換