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

[Ayush_Mittal]

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_type can take one four values(can be more in future, not set yet) and I need to find the most common type of task_type from the table to find the popularity. How should i go about it.

[achinm45 · Accepted solution]

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.

[ankitpatira]

@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]

What I want is to extract that value or a string and not just see it on a graph.

[ryans]

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]

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 · Accepted solution]

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]

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.

[achinm45]

Hi @Ayush_Mittal,

If I can understand you , you want to do folowing :

1) Get task type with most number of occurrences

2) Have generic calculations which can be absorb new task types  in future.

So do following :

1) In DAX, create a new table T1 of Distinct TaskTypes. This shall be formula based to absorb new task types in future

2) Create new column which will measure No of Occurrences against each task type.

3) Now create a measure in T1, which will pull the task type with Most nummber of occurences

For example MaxTaskType =  Lookupvalue(Col_taskType,Col_NoOfOccurences,MAX(Col_NoOfOccurences)

Display this measure in Card to check

Hope it helps you

BR,

Achin

[achinm45 · Accepted solution]

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]

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]

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.