Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
Hi @jtownsend21 ,
You can do this on one of two ways in Query editor or in DAX calculated column:
Query Editor:
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @jtownsend21 ,
You can do this on one of two ways in Query editor or in DAX calculated column:
Query Editor:
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português