Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello Community.
Was wondering how I could extract the minimum and maximum occurrences from a text based column. For example:
[Column 1 (Country)]
Australia
Australia
Australia
Australia
France
France
India
India
China
I want to create a measure that returns Australia - 4 for max. and China - 1 for min.
Thank you.
Solved! Go to Solution.
I would not recommend using TOPN for this for the cases where you have ties!
Here's the Data sample I used expanding on your original dataset...
Country
Australia |
Australia |
Australia |
Australia |
France |
France |
India |
India |
China |
New Zealand |
New Zealand |
New Zealand |
New Zealand |
Japan |
To handles the ties use these - ALL Measures!
Total Count = COUNTA ( 'Table'[Country] )
Top Country Rank = RANKX ( ALLSELECTED ( 'Table'[Country] ), [Total Count],, DESC )
Top Country Name(s) =
CONCATENATEX (
FILTER ( VALUES ( 'Table'[Country] ), [Top Country Rank] = 1 ),
'Table'[Country], ", ", 'Table'[Country], ASC
)
Number of Top Countries =
COUNTROWS ( FILTER ( VALUES ( 'Table'[Country] ), [Top Country Rank] = 1 ) )
Top Country Total Count (per Country!) =
DIVIDE (
CALCULATE (
[Total Count],
TOPN ( 1, ALLSELECTED ( 'Table'[Country] ), [Top Country Rank], ASC )
),
[Number of Top Countries],
0
)
Top Card Measure =
[Top Country Name(s)] & " - "
& [Top Country Total Count (per Country!)]
& IF ( [Number of Top Countries] > 1, " each", "" )
& " for MAX"
To get the Bottom Values change the RANK the 2nd Measure above to ASC instead of DESC ...
And the results...
Hope the helps!
Hi @danielc,
If you want them in separate measures, here is one approach. Not entirely sure if this is what you are after though.....
Max Country = SELECTCOLUMNS( TOPN( 1, SUMMARIZECOLUMNS( 'Table3'[Country] , "Count" , COUNTROWS('Table3') ), [Count], ASC) , "Min Country" , [Country] & " - " & [Count] )
and for MAX
Min Country = SELECTCOLUMNS( TOPN( 1, SUMMARIZECOLUMNS( 'Table3'[Country] , "Count" , COUNTROWS('Table3') ), [Count], DESC) , "Min Country" , [Country] & " - " & [Count] )
Thank you for your response Phil, I tried this code however it returns the count of the first category by alphabetical order.
eg. there are black,brown,blonde colours in my column, there are more counts of brown but the measure returns black. The measure also did the same for another column.
Using this.
MAX EYE HAIR COLOUR = SELECTCOLUMNS( TOPN( 1, SUMMARIZECOLUMNS( 'Missing Children'[HAIR COLOUR], "COUNT", COUNTROWS('Missing Children') ), [HAIR COLOUR], 1) , "MAX COUNTRY", [HAIR COLOUR] & " - " & [COUNT] )
I would not recommend using TOPN for this for the cases where you have ties!
Here's the Data sample I used expanding on your original dataset...
Country
Australia |
Australia |
Australia |
Australia |
France |
France |
India |
India |
China |
New Zealand |
New Zealand |
New Zealand |
New Zealand |
Japan |
To handles the ties use these - ALL Measures!
Total Count = COUNTA ( 'Table'[Country] )
Top Country Rank = RANKX ( ALLSELECTED ( 'Table'[Country] ), [Total Count],, DESC )
Top Country Name(s) =
CONCATENATEX (
FILTER ( VALUES ( 'Table'[Country] ), [Top Country Rank] = 1 ),
'Table'[Country], ", ", 'Table'[Country], ASC
)
Number of Top Countries =
COUNTROWS ( FILTER ( VALUES ( 'Table'[Country] ), [Top Country Rank] = 1 ) )
Top Country Total Count (per Country!) =
DIVIDE (
CALCULATE (
[Total Count],
TOPN ( 1, ALLSELECTED ( 'Table'[Country] ), [Top Country Rank], ASC )
),
[Number of Top Countries],
0
)
Top Card Measure =
[Top Country Name(s)] & " - "
& [Top Country Total Count (per Country!)]
& IF ( [Number of Top Countries] > 1, " each", "" )
& " for MAX"
To get the Bottom Values change the RANK the 2nd Measure above to ASC instead of DESC ...
And the results...
Hope the helps!
Sean, thank you for the helpful response. Does this mean 11 measures in total would be required to have the both the bottom and top country(s) cards on the dashboard? It seems like a lot of work to display one columns max and min occurrences, in the example I was working on I had to do this for 3-4 columns, so 44 measures would be needed...
You can do this with less but you'll have less customization options...
So just use the Top 2 Measures + create 1 for the Bottom Rank (that would be 3)
Plus 1 called Flag
Flag = IF ( OR ([Top Country Rank]=1,[Bottom Country Rank]=1), 1, 0 )
Create a Table and Place Flag in the Visual Level Filters and select is 1 apply filter
Hi @danielc,
Here is an approach in Power query that returns a 2 row table showing the MAX and MIN countries, including counts.
This is more for me playing around in M. The main code is highlighted in M. The other rows are just setup and renaming.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwtLilKzMlMVIrVIYXnVpSYl5yKzvTMS4HKI1jOGZl5QFYsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Country = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Country", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Country"}, {{"Count", each Table.RowCount(_), type number}}), #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Count", Order.Ascending}}), #"Kept First Rows" = Table.FirstN(#"Sorted Rows",1), #"Added Custom" = Table.AddColumn(#"Kept First Rows", "Custom", each "Min"), #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Custom", "Country", "Count"}), #"Kept Last Rows" = Table.LastN(#"Sorted Rows", 1), #"Added Custom1" = Table.AddColumn(#"Kept Last Rows", "Custom", each "Max"), #"Reordered Columns1" = Table.ReorderColumns(#"Added Custom1",{"Custom", "Country", "Count"}), #"Appended Query" = Table.Combine({#"Reordered Columns1", #"Reordered Columns"}), #"Renamed Columns" = Table.RenameColumns(#"Appended Query",{{"Custom", "Type"}}) in #"Renamed Columns"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
104 | |
77 | |
67 | |
63 |
User | Count |
---|---|
144 | |
107 | |
105 | |
82 | |
69 |