Forum Discussion
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_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.
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.
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
10 Replies
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.
- What I want is to extract that value or a string and not just see it on a graph.
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?
