cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
franky94 Frequent Visitor
Frequent Visitor

Custom visual Table using Javascript : Filter Row, Column and single Cell

Hi everybody,

I am writing here hoping to find someone who can help me to find the correct way to develop a custom table visual for power BI.


At this moment I am able to take data from capabilites file using:

 var table: DataViewTable = options.dataViews[0].table

            var columns: DataViewMetadataColumn[] = table.columns;

            var rows: DataViewTableRow[] = table.rows;

After that I manipulate it and I put them in a html table(thead and tbody).

var columnsName = [];

            for (var i = 0; i < columns.length; i++) {
                columnsName[i] = columns[i].displayName
            }

            var stringToBuild = "["

            for (var i = 0; i < rows.length; i++) {
                stringToBuild += "{"
                for (var j = 0; j < columnsName.length; j++) {

                    var colName = columnsName[j]
                    var newLocal = rows[i][j];
                    stringToBuild += "\"" + colName + "\": " + "\"" + newLocal + "\""

                    if (j != columnsName.length - 1) {
                        stringToBuild += ","
                    }
                }
                stringToBuild += "}"
                if (i != rows.length - 1) {
                    stringToBuild += ","
                }
            }
            stringToBuild += "]"
 var valueToRepresent =JSON.parse(stringToBuild)

this.thead.append('tr')
                .selectAll('th')
                .data(columnsName)
                .enter()
                .append("th")
                .text(function (columnsName) { return columnsName; })
                .style("background-color", this.settings.headerFormatting.color)
                .style("font-size", this.settings.headerFormatting.size + "px")
                .style("color",this.settings.headerFormatting.colorFont);

            // create a row for each object in the data
            this.tbody.selectAll('tr')
                .data(valueToRepresent)
                .enter()
                .append('tr');
          
            // create a cell in each row for each column
            var cells = this.tbody.selectAll('tr').selectAll('td')
                .data(function (row) {
                    return columnsName.map(function (column) {
                        return { column: column, value: row[column] };
                    });
                })
                .enter()
                .append('td')
                .text(function (d) { return d.value; });

This way  it works fine.
Now my next goal is to add the possibility to chick on row and filter all the visuals based on that row.

 

Anyone can help me?

1 ACCEPTED SOLUTION

Accepted Solutions
franky94 Frequent Visitor
Frequent Visitor

Re: Custom visual Table using Javascript &colon; Filter Row, Column and single Cell

I solved by my self.

 

Here is the fragment of code I have used, maybe it could help others.

private static getSelectionIds(dataView: DataView, host: IVisualHost): ISelectionId[] {
        return dataView.table.identity.map((identity: data.SelectorsByColumn) => {
            const categoryColumn: DataViewCategoryColumn = {
                source: dataView.table.columns[1],
                values: null,
                identity: [identity]
            };

            return host.createSelectionIdBuilder()
                .withCategory(categoryColumn, 0)
                .createSelectionId();
        });
        }

Using this I am able to filter the table by row. I am talking abot versione 2.5.0. The version for 1.6 suggest by someone on internet is:

private static getSelectionIds(dataView: DataView, host: IVisualHost): ISelectionId[] {
            return dataView.table.identity.map((identity: DataViewScopeIdentity) => {
                const categoryColumn: DataViewCategoryColumn = {
                    source: dataView.table.columns[0],
                    values: null,
                    identity: [identity]
                };
        
                return host.createSelectionIdBuilder()
                    .withCategory(categoryColumn, 0)
                    .createSelectionId();
            });
        }

 

 

 

Thank you anyway,

Francesco Multari

View solution in original post

6 REPLIES 6
v-evelk New Contributor
New Contributor

Re: Custom visual Table using Javascript &colon; Filter Row, Column and single Cell

Hi,

 

Check this article please.

 

Kind Regards,

 

Evgenii Elkin,
Software Engineer
Microsoft Power BI Custom Visuals
pbicvsupport@microsoft.com

franky94 Frequent Visitor
Frequent Visitor

Re: Custom visual Table using Javascript &colon; Filter Row, Column and single Cell

Hi,
I alredy know that link, but there is no explation about dealing with table in capabilities.
That link works only with category.

Could you kindly give me other example?

Thank you,

Francesco

v-evelk New Contributor
New Contributor

Re: Custom visual Table using Javascript &colon; Filter Row, Column and single Cell

Hi Francesco,

 

You should use API 2.5. It contains .withTable method of the builder that must solve selection issues with Table mapping.

 

Kind Regards,

 

Evgenii Elkin,
Software Engineer
Microsoft Power BI Custom Visuals
pbicvsupport@microsoft.com

franky94 Frequent Visitor
Frequent Visitor

Re: Custom visual Table using Javascript &colon; Filter Row, Column and single Cell

Hi, I am using the version API 2.5 but I am little confused using withTable. Could you give me an example?
Moreover, using withTable I can only filter by row number, right?


v-evelk New Contributor
New Contributor

Re: Custom visual Table using Javascript &colon; Filter Row, Column and single Cell

Hi,

 

There is no documentation with examples for a while but yes, you need to set rowNumber and colNumber like here.

 

Kind Regards,

 

Evgenii Elkin,
Software Engineer
Microsoft Power BI Custom Visuals
pbicvsupport@microsoft.com

franky94 Frequent Visitor
Frequent Visitor

Re: Custom visual Table using Javascript &colon; Filter Row, Column and single Cell

I solved by my self.

 

Here is the fragment of code I have used, maybe it could help others.

private static getSelectionIds(dataView: DataView, host: IVisualHost): ISelectionId[] {
        return dataView.table.identity.map((identity: data.SelectorsByColumn) => {
            const categoryColumn: DataViewCategoryColumn = {
                source: dataView.table.columns[1],
                values: null,
                identity: [identity]
            };

            return host.createSelectionIdBuilder()
                .withCategory(categoryColumn, 0)
                .createSelectionId();
        });
        }

Using this I am able to filter the table by row. I am talking abot versione 2.5.0. The version for 1.6 suggest by someone on internet is:

private static getSelectionIds(dataView: DataView, host: IVisualHost): ISelectionId[] {
            return dataView.table.identity.map((identity: DataViewScopeIdentity) => {
                const categoryColumn: DataViewCategoryColumn = {
                    source: dataView.table.columns[0],
                    values: null,
                    identity: [identity]
                };
        
                return host.createSelectionIdBuilder()
                    .withCategory(categoryColumn, 0)
                    .createSelectionId();
            });
        }

 

 

 

Thank you anyway,

Francesco Multari

View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)