Forum Discussion
Helper Iin selector json what is the 't' attribute
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#L2602. 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#L282I added links to my solution for more reference and hope this can also work for your visual.
-JP
Helper IOK, 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;
}
});
Continued ContributorHi 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#L2602. 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#L282I added links to my solution for more reference and hope this can also work for your visual.
-JP
- rdegr
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.
- rdegr
Does not solve the cross-reference issue but does replace some ugly handrolled selector code of mine. Thanks!
- jppp
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
- rdegr
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!