Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ayushmittal
Regular Visitor

Most Recurring Value using Direct Query

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.

1 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. 

View solution in original post

5 REPLIES 5


@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



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

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

image.png

 

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

 

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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.