to make auto count for the 1st higest values using DAX query

Question

In a table I have values Name fruits ---------------- xxx Apple yyy Orange zzz Papaya aaa Pineapple bbb Orange ccc Papaya ddd Papaya fff Apple

I need a measure to count the highest repeated value (which is Papaya it repeats 3 times). I tried with query CALCULATE(COUNT(call_data[fruits]),FILTER(call_data,call_data[fruits]="Papaya"))

but I have to give the value every time in filter. if another fruit count goes higher then I have to change the filter name.

so how could I write a query to count the 1st highest value.

Answer 1

Assuming your table looks like this and its name is call_data

Fruits
Apple
Orange
Papaya
Pineapple
Orange
Papaya
Papaya
Apple

You can use the following calculation. The logic is to replicate the table with fruits and the count of each of them and get the max for the count. In this case 3 for Papaya

DAX Calculation:

CountMaxFruits =
VAR FruitsTable =
    SUMMARIZE (
        call_data,
        call_data[Fruits],
        "@Count", COUNT ( call_data[Fruits] )
    )
VAR MaxFruits =
    MAXX ( FruitsTable, [@Count] )
RETURN
    MaxFruits

Output