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

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.