Display a description in a measure based upon a count of another variable

[jerryr125]

Hi again - I think I defined my requirements incorrectly.

Input:

Flavors-Sold-Data-Table

Location	Flavor
A	Vanilla
B	Strawberry
B	Vanilla
A	Strawberry
B	Strawberry
B	Vanilla
B	Chocolate
A	Vanilla
A	Chocolate
A	Chocolate

I would like show the location with the maximum count of  flavor if the flavor is either Vanilla or Strawberry.

The output in the Measure I am looking for would be "B" since B has the highest count of the flavor Vanilla and Strawberry (4).

Any thoughts?

[jerryr125]

HI - This is working - thank you.

Question - what if I want to have a seperate measure showing the number (in this case 4).

Also, how can I set up image button (like click here for more details) that will go to another page of the table data and only show those four records  ?

[lbendlin]

max count  = 
var a = SUMMARIZE(filter('Table',[Flavor] in {"Vanilla","Strawberry"}),[Location],"ct",COUNTROWS('Table'))
return MAXX(a,[ct])

The button would have to be tied to a sort of filter (bookmark) to be efficient. Otherwise you could just add a static table, and you would need to define what you mean by "more details"

[Deku · Accepted solution]

Var tbl =

Addcolumns(

All(Table),

"@cnt",

Calculate(

Countrows( table ),

Table[flavor] in {"vanilla", "strawberry"}

)

)

Return

Max(

Topn( 1, tbl, [@cnt], desc),

[@cnt]

)

Not sure about the button part

[Deku]

Var tbl =

Addcolumns(

All(Table),

"@cnt",

Calculate(

Countrows( table ),

Table[flavor] in {"vanilla", "strawberry"}

)

)

Return

Max(

Topn( 1, tbl, [@cnt], desc),

Table[location]

)

[lbendlin]

max count location = 
var a = SUMMARIZE(filter('Table',[Flavor] in {"Vanilla","Strawberry"}),[Location],"ct",COUNTROWS('Table'))
return CONCATENATEX(TOPN(1,a,[ct]),[Location])