Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi ,
I am strugling with a very specific use case where I want to remove unnecessary commas created by concatenatex function in summarize columns while grouping two fileds
raw_ds
feature | attribute | project | issue_id |
road | motorwy | abc | 123 |
road | motorwy | pqr | |
road | motorwy | lmn | |
river | centerline | xyz | 456 |
river | centerline | efg | 789 |
railway | broadguage | hij | 333 |
railway | broadguage | tti | |
railway | broadguage | syd | |
railway | broadguage | plm | 346 |
from below query, I got below result:
group_ds =
SUMMARIZECOLUMNS(
raw_ds[feature],
raw_ds[attribute],
"project_concat",CONCATENATEX(raw_ds,raw_ds[project],","),
"issue_id_concat",CONCATENATEX(raw_ds,raw_ds[issue_id],",")
)
feature | attribute | project_concat | issue_id_concat |
road | motorwy | abc,pqr,lmn | 123,, |
river | centerline | xyz,efg | 456,789 |
railway | broadguage | hij,tti,syd,plm | 333,,346 |
However, I dont want unnecessary commas in "issue_id_concat" filed. Do we have solution in DAX?
Solved! Go to Solution.
@Anonymous , Try like
group_ds =
SUMMARIZECOLUMNS(
raw_ds[feature],
raw_ds[attribute],
"project_concat",CONCATENATEX(filter(raw_ds,raw_ds[project] <> "" && not(isblank())) ,raw_ds[project],","),
"issue_id_concat",CONCATENATEX(filter(raw_ds,raw_ds[issue_id] <> "" && not(isblank())) ,raw_ds[issue_id],",")
)
@Anonymous , Try like
group_ds =
SUMMARIZECOLUMNS(
raw_ds[feature],
raw_ds[attribute],
"project_concat",CONCATENATEX(filter(raw_ds,raw_ds[project] <> "" && not(isblank())) ,raw_ds[project],","),
"issue_id_concat",CONCATENATEX(filter(raw_ds,raw_ds[issue_id] <> "" && not(isblank())) ,raw_ds[issue_id],",")
)
It works for me. I learned the use of filter on aggrgation. Thanks!
User | Count |
---|---|
98 | |
90 | |
77 | |
71 | |
64 |
User | Count |
---|---|
114 | |
97 | |
95 | |
68 | |
65 |