Most Recurring Value using Direct Query

[ayushmittal]

I have  a  table with many rows (0.5 Million) thus import method is not an option for use with Power BI, in this table there is a column "Work Type" which has string type values(10 different values). I need to find the most recurring value in this column and display the name of the Work Type that recurrs most.

One more thing the dataa has to be filtered from the gui interface at the report level thus "Group By" is not an option.

So if the input table has following values for Work Type:

Work Type

COFFEE

DOOR

ELECTRICAL

FURNITURE

COFFEE

COFFEE

Then the output should be:

COFFEE

P.S. We need to use Direct Query Mode for the data

In Import mode I have taken count of work types in a new table and taken the max value of the count of each work type's recurrence.

[MattAllington]

---

@ayushmittal wrote:

I have  a  table with many rows (0.5 Million) thus import method is not an option for use with Power BI, 

---

What do you mean by this?  I have workbooks in Power BI with 60 million rows. Why is "import method" not an option?

I would load all the data, put the work type on the axis of a bar chart and write a measure 

Total = Countrows(table)

Put that on the values of your chart

[ayushmittal]

@MattAllington

This is not the only table with huge data in the Dashboard thus the file size becomes too big. Also we require a Live data source connection.

[v-yuezhe-msft]

Hi @ayushmittal,

What is the result after you calculate count of worktype following MattAllington's suggestion? You are able to create measures in DirectQuery mode.

Thanks,
Lydia Zhang

[ayushmittal]

I can create it as ameasure but it cannot be plotted in a graph or used in another column.

Also The Max Column Gives an error as it isn't supported in Columns in DirectQuery Mode.

The above two measures worked perfectly in Import mode but in Direct Query they give errors.

[ayushmittal · Accepted solution]

Hi,

I thank the community for their suggestions, but I figured out the solution in an indirect  manner.

1.  Create a new column(count) in thet very table and then assigned it a value of 1.
2. Plot the work type (text type) column in a multi row  card and then filter using "Top N " type of filtering and add 'count' to the cvalue field under that and put 1 as value for show as Top: .

This resolved the issue and helped in avoiding import of tables.