Forum Discussion

Lucy01's avatar
Lucy01
Helper I
4 months ago
Solved

Find Highest Value in a Text Based Column

Hello, I've asked for help with something like this before and thought I found the solution, but it didn't quite work in the end. This is the previous thread where I'd tried to find a solution:

 

Solved: Re: Find Highest Value of a Column - Microsoft Fabric Community

 

I have one column with five text options and I need to find a way of returning the most frequent option in a card visual, but this always returns whatever comes first alphabetically. 

 

Can anyone suggest a method of finding the most frequently repeated value in a column, when the value is text. Thank you

Need Help
  • Lodha_Jaydeep's avatar
    Lodha_Jaydeep
    4 months ago

    Hi Lucy01,

    Thanks for reaching fabric community, will happy to assist.

     

    This is very solvable in DAX! The reason you're getting the alphabetically first value is because MAX() on a text column returns alphabetical order, not frequency. Here's the correct approach:

    The Fix DAX Measure

    Most Frequent Value = TOPN(
    1,
    ADDCOLUMNS(
        VALUES('Table'[YourColumn]),
        "Freq", CALCULATE(COUNTROWS('Table'))
    ),
    [Freq],
    DESC
)

    But since TOPN returns a table, wrap it properly for a Card visual like this:

     

    Most Frequent Value = MAXX(
    TOPN(
        1,
        ADDCOLUMNS(
            VALUES('Table'[YourColumn]),
            "Freq", CALCULATE(COUNTROWS('Table'))
        ),
        [Freq], DESC
    ),
    'Table'[YourColumn]
)

    Just replace 'Table' and 'YourColumn' with your actual table and column names.

    How It Works

    • VALUES() gets all unique text options in the column
    • ADDCOLUMNS() adds a frequency count next to each unique value
    • TOPN(1, ..., DESC) picks the one with the highest count
    • MAXX() extracts it as a scalar text value for the Card visual

     

    One Thing to Note

    If two values tie on frequency, this will return one of them not both. If ties are possible in your data, let us know and we can add a another logic.

     

    Hope this works! Mark as Accepted Solution if it helps

3 Replies

  • Lodha_Jaydeep's avatar
    Lodha_Jaydeep
    Solution Sage
    4 months ago

    Hi Lucy01,

    Thanks for reaching fabric community, will happy to assist.

     

    This is very solvable in DAX! The reason you're getting the alphabetically first value is because MAX() on a text column returns alphabetical order, not frequency. Here's the correct approach:

    The Fix DAX Measure

    Most Frequent Value = TOPN(
    1,
    ADDCOLUMNS(
        VALUES('Table'[YourColumn]),
        "Freq", CALCULATE(COUNTROWS('Table'))
    ),
    [Freq],
    DESC
)

    But since TOPN returns a table, wrap it properly for a Card visual like this:

     

    Most Frequent Value = MAXX(
    TOPN(
        1,
        ADDCOLUMNS(
            VALUES('Table'[YourColumn]),
            "Freq", CALCULATE(COUNTROWS('Table'))
        ),
        [Freq], DESC
    ),
    'Table'[YourColumn]
)

    Just replace 'Table' and 'YourColumn' with your actual table and column names.

    How It Works

    • VALUES() gets all unique text options in the column
    • ADDCOLUMNS() adds a frequency count next to each unique value
    • TOPN(1, ..., DESC) picks the one with the highest count
    • MAXX() extracts it as a scalar text value for the Card visual

     

    One Thing to Note

    If two values tie on frequency, this will return one of them not both. If ties are possible in your data, let us know and we can add a another logic.

     

    Hope this works! Mark as Accepted Solution if it helps