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

ankitpatira's avatar
ankitpatira
Community Champion
10 years ago

Ayush_Mittal You can use column chart visual. Drop Task_type field into axis and task into Value. This will give you column chart by number of tasks and you can see the most and lowest number of task types.

  • Ayush_Mittal's avatar
    Ayush_Mittal
    Frequent Visitor
    10 years ago
    What I want is to extract that value or a string and not just see it on a graph.
    • ryans's avatar
      ryans
      Helper I
      10 years ago

      If there are only 4 possible values for task type why would it not work to see the string for the task type on the x axis and a count of the times the task type showed up in the data as the y variable? Are the strings for task type very long or something?

      • Ayush_Mittal's avatar
        Ayush_Mittal
        Frequent Visitor
        10 years ago

        That is something not under my control, it can be more than four type values as well, although the size of string is under 14 characters. I want to create a generic measure to simply display the most recurring string. I thoght that the different string types can be assigned codes however since the number of types isn't in my control and also MODE function isn't supported in PBI I'm short on any options.
        Data is to be acquired from a remote server so they can increase the number of types at any moment. The program is limited in that regard.