URGENT! Custom Table Data Limit

Avery — Mon, 06 Dec 2021 16:12:02 GMT

Hello,

I've been struggling with getting my custom table visual to display all the data I need: I know the record limit is 30,000, but fetchMoreData is supposed to allow my Typescript logic to operate on all 100,000 records, correct?

I am iterating over all rows and checking whether two fields are mismatched. There are none in the first 30,000 but I can confirm there are more later on.

Here's my visual.ts:

// Power BI visual to display mismatching ngoc and npsp data "use strict"; import "core-js/stable"; import "./../style/visual.less"; import powerbi from "powerbi-visuals-api"; import VisualConstructorOptions = powerbi.extensibility.visual.VisualConstructorOptions; import VisualUpdateOptions = powerbi.extensibility.visual.VisualUpdateOptions; import IVisual = powerbi.extensibility.visual.IVisual; import EnumerateVisualObjectInstancesOptions = powerbi.EnumerateVisualObjectInstancesOptions; import VisualObjectInstance = powerbi.VisualObjectInstance; import DataView = powerbi.DataView; import VisualObjectInstanceEnumerationObject = powerbi.VisualObjectInstanceEnumerationObject; import DataViewMetadataColumn = powerbi.DataViewMetadataColumn; import DataViewTable = powerbi.DataViewTable; import DataViewTableRow = powerbi.DataViewTableRow; import PrimitiveValue = powerbi.PrimitiveValue; import { VisualSettings } from "./settings"; import IVisualHost = powerbi.extensibility.visual.IVisualHost; export class Visual implements IVisual { private target: HTMLElement; private settings: VisualSettings; private printRow: boolean; private host: IVisualHost; private windowsLoaded: number; private requestAccepted: Boolean; private btnNext: HTMLButtonElement; private btnPrev: HTMLButtonElement; private tbl: DataViewTable; private table: HTMLTableElement; // create base table element constructor(options: VisualConstructorOptions) { this.target = options.element; options.element.style.overflow = 'auto'; this.table = document.createElement("table"); this.target.appendChild(this.table); this.host = options.host; this.windowsLoaded = 0; } // update table display public update(options: VisualUpdateOptions) { console.log(options); this.tbl = options.dataViews[0].table; if (this.tbl.rows.length == 30000) { this.tbl } const tbl: DataViewTable = options.dataViews[0].table; while (this.table.firstChild) this.table.removeChild(this.table.firstChild); this.printRow = false; if (options.dataViews[0].metadata.segment) { this.requestAccepted = !this.host.fetchMoreData() if (!this.requestAccepted) { console.log('Cannot fetch more data. Total rows: ', tbl.rows.length); console.log(this.table.rows.length); return; } else console.log('we have all ', tbl.rows.length, ' rows!'); } // Make headers const header = document.createElement("th"); tbl.columns.forEach((col: DataViewMetadataColumn) => { const headerCol = document.createElement("td"); headerCol.innerText = col.displayName; header.appendChild(headerCol); //header.appendChild(document.createElement("nbsp")); }); this.table.appendChild(header); // Order for columns must be: NGOC Id, NPSP Id, NGOC Field, NPSP Field tbl.rows.forEach((row: DataViewTableRow) => { const tblRow = document.createElement("tr"); // iterate over rows, creating a new row wherever a mismatch is found if (row[0] != null && row[1] != null && row[2].toString() != row[3].toString()) { console.log('found one!'); row.forEach((col: PrimitiveValue) => { const cell = document.createElement("td"); cell.innerText = col.toString(); tblRow.appendChild(cell); this.printRow = true; }); } if (this.printRow) { this.table.appendChild(tblRow); } }); console.log(options); } private static parseSettings(dataView: DataView): VisualSettings { return <VisualSettings>VisualSettings.parse(dataView); } public enumerateObjectInstances(options: EnumerateVisualObjectInstancesOptions): VisualObjectInstance[] | VisualObjectInstanceEnumerationObject { return VisualSettings.enumerateObjectInstances(this.settings || VisualSettings.getDefault(), options); } }

...and Capabilities.json:

{ "dataRoles": [ { "displayName": "Ids on top, fields on bottom", "name": "column", "kind": "Grouping" } ], "dataViewMappings": [ { "conditions": [ { "column": { "max": 4 } } ], "table": { "rows": { "select": [ { "for": { "in": "column" } } ], "dataReductionAlgorithm": { "window": { "count": 30000 } } } } } ], "sorting": { "implicit": { "clauses": [ { "role": "column", "direction": 2 } ] } }, "objects": { "general": { "displayName": "General", "displayNameKey": "formatting", "properties": { "selfFilter": { "type": { "filter": { "selfFilter": true } } } } } } }

Re: URGENT! Custom Table Data Limit

V-lianl-msft — Thu, 09 Dec 2021 02:50:28 GMT

Hi @Avery ,

Dataview total row count is limited to 1,048,576 rows but In segments aggregation mode(default), dataview memory size is limited to 100 MB.

Therefore, dataview size is expected to grow with each update. For example, if a total of 100,000 rows are expected and the window size is set to 10,000, the first update dataview should include 10,000 rows, the second update dataview should include 20,000 rows, and so on.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

topic URGENT! Custom Table Data Limit in Developer

URGENT! Custom Table Data Limit

Re: URGENT! Custom Table Data Limit