topic Re: in selector json what is the 't' attribute in Custom Visuals Development Discussion

in selector json what is the 't' attribute

rdegr — Wed, 10 Oct 2018 14:47:07 GMT

"{"selector":"{\"data\":[\"{\\\"comp\\\":{\\\"k\\\":0,\\\"l\\\":{\\\"col\\\":{\\\"s\\\":{\\\"e\\\":\\\"qtEmergencyRecords\\\"},\\\"r\\\":\\\"Year\\\"}},\\\"r\\\":{\\\"const\\\":{\\\"t\\\":4,\\\"v\\\":2017}}}}\"]}","highlight":false}"

"{"selector":"{\"data\":[\"{\\\"comp\\\":{\\\"k\\\":0,\\\"l\\\":{\\\"col\\\":{\\\"s\\\":{\\\"e\\\":\\\"qtFacility\\\"},\\\"r\\\":\\\"Facility County\\\"}},\\\"r\\\":{\\\"const\\\":{\\\"t\\\":1,\\\"v\\\":\\\"Baker\\\"}}}}\"]}","highlight":false}"

Seems like some sort of type code but doesn't match anything I see in metadata. Please advise. I am trying to build a filter that lets me select table/column, then value, but the generated selectors build filters across all the columns I've added to the control. so I'd like to build simple filters as above manually.

Re: in selector json what is the 't' attribute

v-viig — Thu, 11 Oct 2018 07:37:31 GMT

What is expected behavior for filtering?

Ignat Vilesov,

Software Engineer

Microsoft Power BI Custom Visuals

pbicvsupport@microsoft.com

Re: in selector json what is the 't' attribute

rdegr — Tue, 16 Oct 2018 17:03:14 GMT

I want to first select a column from a dropdown list of the columns added to the control in edit mode, then select values from that column- I have that much working- then filter on the selected values for the selected column only. The problem is that once I have multiple columns and switch between them the code generates filters that filter across multiple columns so I want to build my own filter string or otherwise only filter the selected column. Here is my selection method

        public setSelectedByValues() {
            let arr:any[] = [];

            for (let i = 0; i < this.dropdownByValues.selectedOptions.length; i++) {
                arr.push(this.selectionIds[this.dropdownByValues.selectedOptions[i].value+'']);
            }

            this.selectionManager.clear().then(()=> {
                this.selectionManager.select(arr, false).then((ids: ISelectionId[])=> {
                    debugger;
                    this.selectionManager.applySelectionFilter();
                });
            });
                
        }

and this.selectionIds populated as follows:

                    let cat = this.dataView.categorical.categories[index-1];

                    cat.values.forEach((val:any, valIndex:number) => {

                        if (!hash[val+'']) {
                            hash[val+''] = true;
                        
                            let optV:HTMLOptionElement = document.createElement("option");
                            optV.text = val;
                            optV.value = val;
            
                            this.dropdownByValues.options.add(optV);

                            this.selectionIds[val+''] = this.host.createSelectionIdBuilder()
                                .withCategory(cat, valIndex)
                                .createSelectionId();
                        }
                    });

I think the real issue may be in this code. Here is an example of a selector where all I want is to filter on year but I also get facility.

{"selector":"{\"data\":[\"{\\\"and\\\":{\\\"l\\\":{\\\"comp\\\":{\\\"k\\\":0,\\\"l\\\":{\\\"col\\\":{\\\"s\\\":{\\\"e\\\":\\\"qtEmergencyRecords\\\"},\\\"r\\\":\\\"Year\\\"}},\\\"r\\\":{\\\"const\\\":{\\\"t\\\":4,\\\"v\\\":2017}}}},\\\"r\\\":{\\\"comp\\\":{\\\"k\\\":0,\\\"l\\\":{\\\"col\\\":{\\\"s\\\":{\\\"e\\\":\\\"qtFacility\\\"},\\\"r\\\":\\\"Facility County\\\"}},\\\"r\\\":{\\\"const\\\":{\\\"t\\\":1,\\\"v\\\":\\\"Alachua\\\"}}}}}}\"]}

Re: in selector json what is the 't' attribute

rdegr — Tue, 16 Oct 2018 18:10:26 GMT

When I manually build "selector" strings identical to the ones I get if I only have a single column added to the control, then I get

Uncaught TypeError: e.hasIdentity is not a function

this.selectionManager.select(arr, false)

Re: in selector json what is the 't' attribute

rdegr — Tue, 16 Oct 2018 22:02:48 GMT

Trying to build the whole selector object, but don't know what the classes are and still get the same error.

                    let cat = this.dataView.categorical.categories[index-1];

                    cat.values.forEach((val:any, valIndex:number) => {

                        if (!hash[val+'']) {
                            hash[val+''] = true;
                        
                            let optV:HTMLOptionElement = document.createElement("option");
                            optV.text = val;
                            optV.value = val;
            
                            this.dropdownByValues.options.add(optV);

                            // this.selectionIds[val+''] = this.host.createSelectionIdBuilder()
                            //     .withCategory(cat, valIndex)
                            //     .createSelectionId();

                            var expr:any = cat.source.expr;
                            var typ:any = cat.source.type;
                            var typVal = (typ.primitiveType == 1 ? "\"" + val + "\"" : val);
                            var dm:string = expr.source.entity + "." + expr.ref;

                            var obj:any = {
                                expr: {
                                    comparison: 0,
                                    kind: 13,
                                    left: { kind: 2, ref: expr.ref, source: expr.source },
                                    right: { kind:17, type: typ, value: typVal }
                                },
                                key: "{\"comp\":{\"k\":0,\"l\":{\"col\":{\"s\":{\"e\":\"" + expr.source.entity + "\"},\"r\":\"" + expr.ref + "\"}},\"r\":{\"const\":{\"t\":" + typ.primitiveType + ",\"v\":" + typVal + "}}}}",
                                kind: 1  
                            };

                            this.selectionIds[val+''] = {
                                highlight: false,
                                key: "{\"selector\":\"{\\\"data\\\":[\\\"{\\\\\\\"comp\\\\\\\":{\\\\\\\"k\\\\\\\":0,\\\\\\\"l\\\\\\\":{\\\\\\\"col\\\\\\\":{\\\\\\\"s\\\\\\\":{\\\\\\\"e\\\\\\\":\\\\\\\"" + expr.source.entity + "\\\\\\\"},\\\\\\\"r\\\\\\\":\\\\\\\"" + expr.ref + "\\\\\\\"}},\\\\\\\"r\\\\\\\":{\\\\\\\"const\\\\\\\":{\\\\\\\"t\\\\\\\":" + typ.primitiveType + ",\\\\\\\"v\\\\\\\":" + typVal + "}}}}\\\"]}\",\"highlight\":false}",
                                keyWithoutHighlight: "{\"selector\":\"{\\\"data\\\":[\\\"{\\\\\\\"comp\\\\\\\":{\\\\\\\"k\\\\\\\":0,\\\\\\\"l\\\\\\\":{\\\\\\\"col\\\\\\\":{\\\\\\\"s\\\\\\\":{\\\\\\\"e\\\\\\\":\\\\\\\"" + expr.source.entity + "\\\\\\\"},\\\\\\\"r\\\\\\\":\\\\\\\"" + expr.ref + "\\\\\\\"}},\\\\\\\"r\\\\\\\":{\\\\\\\"const\\\\\\\":{\\\\\\\"t\\\\\\\":" + typ.primitiveType + ",\\\\\\\"v\\\\\\\":" + typVal + "}}}}\\\"]}\"}",
                                selector: {
                                    data: [{
                                        obj
                                    }]
                                },
                                selectorsByColumn: {
                                    dataMap: {
                                    }
                                }
                            };

                            this.selectionIds[val+''].selectorsByColumn.dataMap[dm] = obj;
                        }
                    });

Re: in selector json what is the 't' attribute

rdegr — Tue, 16 Oct 2018 21:56:36 GMT

OK, after much tracing through the code I am beginning to make some progress. The missing piece of my object construction was encoded values. Unfortunately, what I find in https://github.com/deldersveld/PowerBI-visuals/blob/master/src/Clients/VisualsData/semanticQuery/primitiveValueEncoding.ts suggests I need to distinguish between "decimal" and "double" numeric types and nothing in the category source type makes that distinction. I could very much use a mapping for the category.source.type.underlyingType values but I have not found it yet.

                            if (cat.source.type.integer) {
                                obj.expr.right.valueEncoded = val + "L";
                            }
                            else if (cat.source.type.numeric) {
                                obj.expr.right.valueEncoded = val + "D"; // TODO decimal v double
                            }
                            else if (cat.source.type.text)
                            {
                                obj.expr.right.valueEncoded = "'" + val.replace("'", "''") + "'";
                            }
                            else
                            {
                                obj.expr.right.valueEncoded = typVal;
                            }

Complete construction that now gives desired behavior for one integer and one text column. Desired behavior being to ignore any other column but the selected column.

                    let cat = this.dataView.categorical.categories[index-1];

                    cat.values.forEach((val:any, valIndex:number) => {

                        if (!hash[val+'']) {
                            hash[val+''] = true;
                        
                            let optV:HTMLOptionElement = document.createElement("option");
                            optV.text = val;
                            optV.value = val;
            
                            this.dropdownByValues.options.add(optV);

                            // this.selectionIds[val+''] = this.host.createSelectionIdBuilder()
                            //     .withCategory(cat, valIndex)
                            //     .createSelectionId();

                            let expr:any = cat.source.expr;
                            let typ:any = cat.source.type;
                            let typVal = (typ.primitiveType == 1 ? "\"" + val + "\"" : val);
                            let dm:string = expr.source.entity + "." + expr.ref;

                            expr.source.accept = function(e,t) {
                                return e.visitEntity(expr.source, t);
                            }

                            

                            let obj:any = {
                                expr: {
                                    comparison: 0,
                                    kind: 13,
                                    left: { kind: 2, ref: expr.ref, source: expr.source, accept(e,t) { return e.visitColumnRef(obj.expr.left, t); } },
                                    right: { kind:17, type: typ, value: typVal, accept(e,t) { return e.visitConstant(obj.expr.right, t); } },
                                    accept(e,t) {
                                        return e.visitAnd(obj.expr, t);
                                    }
                                },
                                key: "{\"comp\":{\"k\":0,\"l\":{\"col\":{\"s\":{\"e\":\"" + expr.source.entity + "\"},\"r\":\"" + expr.ref + "\"}},\"r\":{\"const\":{\"t\":" + typ.primitiveType + ",\"v\":" + typVal + "}}}}",
                                kind: 1
                            };

                            if (cat.source.type.integer) {
                                obj.expr.right.valueEncoded = val + "L";
                            }
                            else if (cat.source.type.numeric) {
                                obj.expr.right.valueEncoded = val + "D"; // TODO decimal v double
                            }
                            else if (cat.source.type.text)
                            {
                                obj.expr.right.valueEncoded = "'" + val.replace("'", "''") + "'";
                            }
                            else
                            {
                                obj.expr.right.valueEncoded = typVal;
                            }

                            let selectorObj = {
                                highlight: false,
                                key: "{\"selector\":\"{\\\"data\\\":[\\\"{\\\\\\\"comp\\\\\\\":{\\\\\\\"k\\\\\\\":0,\\\\\\\"l\\\\\\\":{\\\\\\\"col\\\\\\\":{\\\\\\\"s\\\\\\\":{\\\\\\\"e\\\\\\\":\\\\\\\"" + expr.source.entity + "\\\\\\\"},\\\\\\\"r\\\\\\\":\\\\\\\"" + expr.ref + "\\\\\\\"}},\\\\\\\"r\\\\\\\":{\\\\\\\"const\\\\\\\":{\\\\\\\"t\\\\\\\":" + typ.primitiveType + ",\\\\\\\"v\\\\\\\":" + typVal + "}}}}\\\"]}\",\"highlight\":false}",
                                keyWithoutHighlight: "{\"selector\":\"{\\\"data\\\":[\\\"{\\\\\\\"comp\\\\\\\":{\\\\\\\"k\\\\\\\":0,\\\\\\\"l\\\\\\\":{\\\\\\\"col\\\\\\\":{\\\\\\\"s\\\\\\\":{\\\\\\\"e\\\\\\\":\\\\\\\"" + expr.source.entity + "\\\\\\\"},\\\\\\\"r\\\\\\\":\\\\\\\"" + expr.ref + "\\\\\\\"}},\\\\\\\"r\\\\\\\":{\\\\\\\"const\\\\\\\":{\\\\\\\"t\\\\\\\":" + typ.primitiveType + ",\\\\\\\"v\\\\\\\":" + typVal + "}}}}\\\"]}\"}",
                                selector: {
                                    data: []
                                },
                                selectorsByColumn: {
                                    dataMap: {
                                    }
                                },
                                getKey() {
                                    return selectorObj.key;
                                },
                                getKeyWithoutHighlight() {
                                    return selectorObj.keyWithoutHighlight;
                                },
                                getSelector() {
                                    return selectorObj.selector;
                                },
                                getSelectorsByColumn() {
                                    return selectorObj.selectorsByColumn;
                                },
                                hasIdentity() {
                                    return true;
                                }
                            };

                            selectorObj.selectorsByColumn.dataMap[dm] = obj;
                            selectorObj.selector.data[0] = obj;

                            this.selectionIds[val+''] = selectorObj;
                        }
                    });

Re: in selector json what is the 't' attribute

jppp — Wed, 17 Oct 2018 06:22:09 GMT

Hi @rdegr,

I had a similar request andwas able to solve it in the following was:

1. I create an array of custom identities, in this case it uses the category as text, but that can be changed to SQExprBuilder.number if needed.

let categoryIdentities = categories.map((category) => {
    let sqlExpr = powerbi["data"].SQExprBuilder.equal(dataView.metadata.columns[0].expr, powerbi["data"].SQExprBuilder.text(category));
    return powerbi["data"].createDataViewScopeIdentity(sqlExpr);
});

# ref: https://github.com/liprec/powerbi-boxWhiskerChart/blob/89179879a7209b030245fd05c317eee2034db394/src/boxWhiskerChart.ts#L260

2. When I loop thru the individual catagories (=i variable)I use the following code to create the correct SelectionId identifier:

let selectionId = new SelectionId({ data: [ categoryIdentities[i] ] }, false);

# ref: https://github.com/liprec/powerbi-boxWhiskerChart/blob/89179879a7209b030245fd05c317eee2034db394/src/boxWhiskerChart.ts#L282

I added links to my solution for more reference and hope this can also work for your visual.

-JP

Re: in selector json what is the 't' attribute

rdegr — Wed, 17 Oct 2018 13:18:18 GMT

Very cool. I will try this out. My way still has an issue of recordsets growing as more columns are added (something trying to cross-reference) which this may work around.

Re: in selector json what is the 't' attribute

rdegr — Wed, 17 Oct 2018 14:29:14 GMT

Does not solve the cross-reference issue but does replace some ugly handrolled selector code of mine. Thanks!

Re: in selector json what is the 't' attribute

jppp — Thu, 18 Oct 2018 14:37:59 GMT

As far as I know you can add extra columns to the `sqlExpr` variable and combine them with `powerbi["data"].SQExprBuilder.and` method.

There is no need to construct this kind of filters/selectors by yourself as all the needed methods of `SQExprBuiler` are exposed to be used with a Custom Visual. Take a look at the repository of the old Power BI visuals: https://github.com/avontd2868/PowerBI-visuals/tree/master/src/Clients/Data/semanticQuery for more details.

-JP

Re: in selector json what is the 't' attribute

rdegr — Thu, 18 Oct 2018 14:41:27 GMT

I'm not trying to add extra columns, but to work around them being added when I don't want. Your SQLExprBuilder code was helpful to me, it replaced about 20 lines where I was building a selector object from scratch. Thanks!

Re: in selector json what is the 't' attribute

rdegr — Thu, 18 Oct 2018 15:04:18 GMT

I've solved my problem and will select the most helpful of these replies as (part of) the solution. For the issue of being limited to 30K values I preloaded values for known columns.

module DistinctValues {

    export var qtEmergencyRecords:any = {};
    
    qtEmergencyRecords["Year"] = [ 2016, 2017 ];    

    export var qtFacility:any = {};

    qtFacility["Facility County"] = [
        "Alachua",
        "Baker",
        "Bay",
        "Bradford",
        ...

then in processing a category "cat"

                    let arr = cat.values;
                    if (DistinctValues[expr.source.entity])
                    {
                        let dvs = DistinctValues[expr.source.entity];

                        if (dvs[expr.ref] && dvs[expr.ref].length)
                        {
                            arr = dvs[expr.ref];
                        }
                    }
                    
                    arr.forEach((val:any, valIndex:number) => {

Re: in selector json what is the 't' attribute

jppp — Thu, 18 Oct 2018 15:04:31 GMT

You should be able to construct all needed filters/selectors with the `SQExprBuilder` as Power BI itself is using the same.

Or you are trying to create something that Power BI doesn't understand.

-JP

Re: in selector json what is the 't' attribute

rdegr — Thu, 18 Oct 2018 15:10:00 GMT

My problem is it wants to cross-reference the columns I've added. If I add a column with two distinct values, then another with 100, then I get 200 "values" for the first column, one for every combo of the two. And

                            this.selectionIds[val+''] = this.host.createSelectionIdBuilder()
                                 .withCategory(cat, valIndex)
                                 .createSelectionId();

creates filters that apply to both columns.

I used your example to build a selector for just the column I care about.

Re: in selector json what is the 't' attribute

v-viig — Fri, 19 Oct 2018 06:57:34 GMT

Please note SQExprBuilder is not documented as official API. It might be removed or changed without any notification.

If you need some specific API please send us details to pbicvsupport@microsoft.com

Otherwise, you might get unexpected breaks.

Ignat Vilesov,

Software Engineer

Microsoft Power BI Custom Visuals

pbicvsupport@microsoft.com

Re: in selector json what is the 't' attribute

rdegr — Fri, 19 Oct 2018 13:13:47 GMT

Thank you, that is useful to know. I'm on 1.5 currently as some samples I looked at early on didn't work with 2.x. This is for a single app of ours, we don't plan to try to publish it publicly.

Re: in selector json what is the 't' attribute

v-viig — Mon, 22 Oct 2018 07:29:25 GMT

Got you. API 2.1.0 removed use internal interfaces to make API lighter.

Ignat Vilesov,

Software Engineer

Microsoft Power BI Custom Visuals

pbicvsupport@microsoft.com