How to limit rows in a table by a constant value?

[Anonymous]

Hello!

I am facing an issue where by default, I have too many records in a table and with additional calculations, it simply breaks visual.

This is an employee related table, where lots of employee's statistical data is being shown. A filter is also included on this visual that makes it so when user checks this report, they can only see their statuses on a monthly basis, rather than everyone being able to check everyone's data.

I cannot use the Filter pane, as filtering in this cases by Top N wouldn't make sense. If I filter by the Top N of EmployeeName, or BuildingEntry, or other thing, it does reduce the amount of data, but it shows blank for users on PBI Dashboard.

I was wondering if it is possible to "tell" Power BI's table to only output a constant of 100 rows and not more? This way, employees can still 100 months worth of data, which definitel doesn't exceed the visual and it also runs fine in Power BI Desktop.

I tried a couple RANKX DAX, but didn't work (the formulas failed).

Let me provide some dummy data:

Table's name is: EmployeeData

Columns:

-Calendar.MonthYear, 

-EmployeeData.EmployeeId

-EmployeeData.EmployeeName

-EmployeeData.NumberOfEntries (measure)

-EmployeeData.Quota (measure)

-EmployeeData.Rank

-EmployeeData.Seniority

Relationships between the 2 tables are set. Dataset is not imported into PowerBI Desktop, the data comes from Views. Don't know if these are relevant informations, but I guess it doesn't hurt to mention

[parry2k]

@Anonymous what is the measure definition? How big is the data? Not sure given this simple model, it should be an issue, although depends on the measure definition. 

[Anonymous]

The measures are slightly complex, few SUMX and evaluations based on other records. But even in this case, isn't there a way to manually set the records?

[Jpss]

Hey @tvadoc 
Inorder to limit the data to displayed to users still have it accessible to them, we need to have some kind of filters in place. 

so my understanding of your case is :

1 ) You have already set the report that each employee see their own data

2 ) even the report is set to display one month of data Per Employee, it is still huge,  so you want to limit it to just 100 rows. 

in such scenarios, My approach would be following:

1) Consider creating a slicer with custom values that allows users to select , for example : 1 Month, 6 Months, 10 Months etc..  ( This video will help :https://www.youtube.com/watch?v=Xi86HHEaY_M), and may be publish the report only for 1 Month, so that will become a default for when user opens the report for first time. 
- you can consider expanding on this and look at ways, how this technique can fit your specific use case

2 ) if you still need to communicate several months of data, consider creating either a summary table.  that for example shows yearly data or Quarterly, and introduce a drill down so when they drill down per row, they only see months for the year selected or quarter selected.

3 )  like mentioned above, its a good idea to break it down to two separate views. first one can be summary view,  and KPI's and other high level data, and then either a details page, with a single Qtr or year's data, or a drill down or even a tooltip on summary page. 

These techniques can help you share the information to users more efficiently and without causing any data load issues


If you find this information helpful.  Consider marking this as a solution

[Anonymous]

Thank you for the reply!

-Previous designer already created a filter called IndividualFlag. If this Flag is set to 1, then all data disappears, and only the data that the employees can see based on their EmployeeID will become available to them.  This is the default in the report: A table that's blank due to this Flag. If this filter is removed, then every data becomes available, or would become, if the report could handle this much data and calculation. 
-If the report is set to like 4-6 months, it still works. It outputs in general every single employee's data for this amount of months, and of course with this IndividualFlag enabled (set to 1), then users on the dashboard can see their data for 4-6 months. There is a Year-Month slicer added to be able to select only a certain amount of time.

-Users individually only see their monthly data, so in the table, 1 row per month (Monthly data). So if I have 2 years worth of data, it would still be only between 2023 Jan - 2024 Sep. However, in the report, because I made measures and added them to the table visual, after a certain records, it breaks visual. Because as I mentioned, the way I need to develop is to first disable this IndividualFlag filter, do the changes, reenable IndividualFlag filter, publish.