Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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.
@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
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
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.
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |