Forum Discussion
Partition Over - Lookup
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.
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
2 Replies
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
