Partition Over - Lookup

[jtownsend21]

I have the following sample data: 

Property	Owner Name	Code	County
1	ABC Co	123	Dallas
2	ABC Co		Tarrant
3	Ex. Inc	456	Dallas
4	Ex. Inc		Collin
5	XYZ LLC		Harris
6	XYZ LLC	789	Dallas
7	ABC Co		Harris
8	Ex. Inc		Tarrant
9	Ex. Inc	456	Dallas
10	ABC Co	123	Dallas

I would like to have the code column populate the code for each company based on the value associated with that company regardless of the county. E.g. all the ABC Co would populate with '123'. 

I have tried to do a calculated column with the following, but It is not working like I hoped. 

*OwnerCode* = 
VAR _OwnerName = 'Table'[OwnerName]
RETURN
    MAXX(
        FILTER(
            'Table',
            'Table'[OwnerName]  = _OwnerName &&
            NOT(ISBLANK('All Counties'[Code]))
        ),
        MAX('Table'[Code])
)

I thought about a a lookup and creating a summary table in the function, but not sure exactly how to do that. Open to other ideas. Thank you. 

[MFelix · Accepted solution]

Hi @jtownsend21 ,

You can do this on one of two ways in Query editor or in DAX calculated column:

Query Editor:

• Sort Columns by OwerName (no matter the sort)
• Sort again by Code (Descending order)
• Fill Down the code
• Reorder by Property

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJ0clZwzgcyDI2MgaRLYk5OYrFSrE60khGyrAIQhyQWFSXmlYAlQWpdK/QUPPOSgSwTUzNUvSYo0iDNzvk5OZl5YElTIDciMkrBx8cZKukBNDkTotMMRdLcwhLVYHN0RyFptcCwFNnFlvhdbGiAOzBiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Property = _t, #"Owner Name" = _t, Code = _t, County = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Property", Int64.Type}, {"Owner Name", type text}, {"Code", Int64.Type}, {"County", type text}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Owner Name", Order.Ascending}, {"Code", Order.Descending}}),
#"Filled Down" = Table.FillDown(#"Sorted Rows",{"Code"}),
#"Sorted Rows1" = Table.Sort(#"Filled Down",{{"Property", Order.Ascending}})
in
#"Sorted Rows1"

Calculated Column in DAX:

Column = CALCULATE(MAX('Table'[Code]);ALLEXCEPT('Table';'Table'[Owner Name]))

Regards,

MFelix

[jtownsend21]

@MFelix  so simple. I was way over complicating that problem. 

Thank you.