Assign value from one Column in a row to another column in other rows based on a condition

[PrasannaJ]

Hi,

I need help in the following:

I have a table like below

Site	URL	Traffic
A	url1	7
A	url2	3
B	url3	9
A	url4	6
B	url5	0

I want to add a 'Preferred URL' column, that provides a possible better URL based on `Traffic` within the `Site`, like below:

Site	URL	Traffic	Preferred URL
A	url1	7	url1
A	url2	3	url1
B	url3	9	url3
A	url4	6	url1
B	url5	2	url3

The logic is, within the site, the URL that gets max traffic is treated as the preferred URL for other URLs within the site.

For example, Site 'A' url1 has max traffic = 7, hence it becomes prefer url for Site A url2 & Site A url4. Similarly, Site B url3 becomes the preferred URL for Site B url 5.

Thanks.

[PrasannaJ]

Awesome. Thanks @kegoosse . I made a small change based on your input as follows:

Highest traffic per site =

VAR MaxTraffic =

    CALCULATE(

        MAX('Table'[Traffic]),

        ALLEXCEPT('Table','Table'[Site])

        )

RETURN

    CALCULATE(

        FIRSTNONBLANK('Table'[Traffic],TRUE()),

        FILTER('Table','Table'[Site]=EARLIER('Table'[Site]) && 'Table'[Traffic]=MaxTraffic )

        )

[kegoosse · Accepted solution]

@PrasannaJ 

This DAX might work if the Traffic values per site are unique

Highest traffic per site =
VAR MaxTraffic = CALCULATE(
    MAX(Table[Traffic]),
    FILTER(
        Table,
        Table[Site] = EARLIER(Table[Site])
    )
)
RETURN
CALCULATE(
    FIRSTNONBLANK(Table[URL],TRUE()),
    FILTER(
        Table,
        Table[Site] = EARLIER(Table[Site])
        &&
        Table[Traffic] = MaxTraffic
    )
)

If Traffic values per site can be repeated, you'll have to add a helper column which converts the traffic values to unique values (for example by adding and index multiplied by 0,000001). Then replace Table[Traffic] with Table[Helper column].