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.
Hello all,
I require an assistance regarding a categorization calculated column I am creating. I am trying to do this in DAX, but if there are any workareound, it would be great.
Below is my issue, I have 3 columns in the dataset, cargo , customer name and location. I would like to create another column that:
1. would repeat the Location if not Blank
2. If blank , would analyze how many times a location was used for the same customer, rank it and assign the most used one.
3. In case that there are customers that had same number of cargoes sent to different locations , choose one based on the alphabetical sequence (A-Z)
Cargo | Customer Name | Location | Desired Calculated Column |
A | Mark | Asia | Asia |
B | Mark | Asia | Asia |
C | Mark | Europe | Europe |
D | Mark | Blank | Asia (based on the other data, Asia was the highest sent Location) |
E | Louis | Asia | Asia |
F | Louis | Asia | Asia |
G | Louis | Blank | Asia (based on the other data, Asia was the highest sent Location) |
H | Julie | Europe | Europe |
I | Julie | America | America |
J | Julie | Blank | America (it is 50 50 for America and Europe, so consider the first one (based on A-Z) |
Thank you in advance!
Solved! Go to Solution.
@Anonymous create this calculated column:
Column =
IF(
'Table'[Location] <> "", 'Table'[Location],
VAR _current_customer = 'Table'[Customer Name]
VAR _tbl =
CALCULATETABLE(
ADDCOLUMNS(
VALUES('Table'[Location]),
"@Cargos", CALCULATE(COUNTROWS('Table'))
),
'Table'[Customer Name] = _current_customer,
'Table'[Location] <> "",
REMOVEFILTERS('Table')
)
VAR _max_cargos = MAXX(_tbl, [@Cargos])
VAR _filtered_tbl = FILTER(_tbl, [@Cargos] = _max_cargos)
VAR _remove_equals = TOPN(1,_filtered_tbl, 'Table'[Location] , ASC)
VAR _result = CONCATENATEX(_remove_equals, 'Table'[Location])
RETURN
_result
)
@Anonymous create this calculated column:
Column =
IF(
'Table'[Location] <> "", 'Table'[Location],
VAR _current_customer = 'Table'[Customer Name]
VAR _tbl =
CALCULATETABLE(
ADDCOLUMNS(
VALUES('Table'[Location]),
"@Cargos", CALCULATE(COUNTROWS('Table'))
),
'Table'[Customer Name] = _current_customer,
'Table'[Location] <> "",
REMOVEFILTERS('Table')
)
VAR _max_cargos = MAXX(_tbl, [@Cargos])
VAR _filtered_tbl = FILTER(_tbl, [@Cargos] = _max_cargos)
VAR _remove_equals = TOPN(1,_filtered_tbl, 'Table'[Location] , ASC)
VAR _result = CONCATENATEX(_remove_equals, 'Table'[Location])
RETURN
_result
)
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 |
---|---|
85 | |
83 | |
66 | |
60 | |
57 |
User | Count |
---|---|
188 | |
111 | |
105 | |
78 | |
71 |