Forum Discussion

alex814546's avatar
alex814546
New Member
1 year ago
Solved

Finding the most popular code in a column

Hello,

 

I work on PBI Desktop, and in my table I have a column named TYPE, and another named CODE.

There are several TYPES and several CODES, I want to regroup them by families.

 

To do so, I need to create a new column, where I display for each line the most popular CODE in its category of TYPE.

 

An example :

TYPECODENEW COLUMN
A11
A11
B44
A31
B44
B34
A11

 

As you may guess, the aim of the NEW COLUMN is to have a unique CODE in each category of TYPE (the most popular CODE in this category of TYPE).

 

Could you please help me to write the DAX formula of the NEW COLUMN?

 

Thanks!

Need Help

5 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    1 year ago

    Hi alex814546  - You can achieve this in Power BI by creating a calculated column that assigns the most frequent CODE for each TYPE.

     

     

     

    Most_Popular_Code =
    VAR CurrentType = 'Tab2'[TYPE]
    VAR CodeCounts =
        ADDCOLUMNS(
            SUMMARIZE(
                FILTER('Tab2', 'Tab2'[TYPE] = CurrentType),
                'Tab2'[CODE]
            ),
            "CodeCount", CALCULATE(COUNTROWS('Tab2'))
        )
    VAR MostFrequentCode =
        MAXX(
            TOPN(1, CodeCounts, [CodeCount], DESC),
            [CODE]
        )
    RETURN
    MostFrequentCode
     
     
     
    Hope this helps.
    • alex814546's avatar
      alex814546
      New Member
      1 year ago

      Hello, thank you for your quick response.

      I can see on your picture that it works, but unfortunately, it just copy/pastes the column CODE for me...

       

      Thanks

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    1 year ago

    Hi,

    These calculated column formulas work

    Calculated Column 1 = CALCULATE(COUNTROWS(Data),FILTER(Data,Data[TYPE]=EARLIER(Data[TYPE])&&Data[CODE]=EARLIER(Data[CODE])))

    Calculated column 2 = LOOKUPVALUE(Data[CODE],Data[Calculated Column 1],CALCULATE(MAX([Calculated Column 1]),FILTER(Data,Data[TYPE]=EARLIER(Data[TYPE]))),Data[TYPE],Data[TYPE])

    Hope this helps.

     

    • v-aatheeque's avatar
      v-aatheeque
      Icon for Community Support rankCommunity Support
      1 year ago

      Hi alex814546 

      If our response addressed by the community member for your query, please mark it as Accept Answer and click Yes if you found it helpful.

      Should you have any further questions, feel free to reach out.
      Thank you for being a part of the Microsoft Fabric Community Forum!