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

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.

ryans's avatar
ryans
Helper I
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

  • dt945's avatar
    dt945
    New Member
    10 years ago

    This is a great solution that I've modified and applied to my project. However, filtering on the referenced/original table (by year in my context) is not modifying the count in this table. From my understanding, this should work fine since I am using the original data to evaluate COUNTA.

     

    modetable = 
ADDCOLUMNS (
    SUMMARIZE ( 'SFCensusData', 'SFCensusData'[dage]), 
    "Count", CALCULATE( sum( SFCensusData[bikers]))
)

    Where bikers is simply a 1 or 0 depending on if a resident is a biker or not. So the bikers are split up by age group and summed. In my visualization I would like to give the user the ability to splice by year and find the most freqent age group.