Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rdegr
Helper I
Helper I

in selector json what is the 't' attribute

"{"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.

1 ACCEPTED SOLUTION

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

View solution in original post

16 REPLIES 16
v-viig
Community Champion
Community Champion

What is expected behavior for filtering?

 

Ignat Vilesov,

Software Engineer

 

Microsoft Power BI Custom Visuals

pbicvsupport@microsoft.com

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\\\"}}}}}}\"]}

 

 

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

 

at

 

this.selectionManager.select(arr, false)

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;
                        }
                    });

 

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/pri... 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;
                        }
                    });

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

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

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

v-viig
Community Champion
Community Champion

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

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.

v-viig
Community Champion
Community Champion

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

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!

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

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.  

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) => {

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors