Forum Discussion

Ayush_Mittal's avatar
Ayush_Mittal
Frequent Visitor
10 years ago
Solved

Find most reccuring value(Text or string) in a column?

I'm new to the software and am wondering how to find the most reccuring item in a column. Let's say that I have a Work schedule table with task_id, work_id, task and task_type as fields. Now task_typ...
  • achinm45's avatar
    achinm45
    10 years ago

    Hi,

    I missed one step. You have to create relationship between new Table T1 and original table by going into Relationship tab.

    Then it will work.

  • ryans's avatar
    ryans
    10 years ago

    OK,

     

    Played around with one of my datasets and I believe the following would work:

     

    First Create a Summary Table based on the following pattern:

    Table1Summary =
ADDCOLUMNS (
    SUMMARIZE ( 'table1', 'table1'[task_type] ),
    "Count", CALCULATE ( COUNTA ( 'table1'[task_type] ) )
)

    Then Create a Custom Column on that table in order to flag the max value:

    IsMax =
SWITCH ( TRUE (), Table1Summary[Count] = MAX ( Table1Summary[Count] ), 1, 0 )

    This will create a flag with a value of 1 in a new column.

     

    You can then use a viz like the multi-row card to add the string and the count of hits against that string. Use the IsMax flag to filter the viz to only display values where is max = "1"

     

    You may be able to combine that into one Dax statement but my dax is not that good yet.

     

    I hope this helps.

     

    Edit - Made a couple adjustments to the dax

achinm45's avatar
achinm45
Advocate IV
10 years ago

Hi,

I missed one step. You have to create relationship between new Table T1 and original table by going into Relationship tab.

Then it will work.

Ayush_Mittal's avatar
Ayush_Mittal
Frequent Visitor
10 years ago

Thanks for the idea. Didn't know about DAX and table creation using the codes like that but you gave me the perfect idea. I created a new table to take values from my primary table filtered it using the GUI and eliminated duplicates. Now will use count occurances.

  • ryans's avatar
    ryans
    Helper I
    10 years ago
    So you used the Get Data query editor? The summary table I generated in dax could be done the same way in the Query Editor with the Group By tool. Sort of funny how many different ways you can approach a problem with power bi.