Categorize Cargo Destination based on Previous Historical Data

[Anonymous]

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!

[SpartaBI · Accepted 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
)

      

Showcase Report – Contoso By SpartaBI