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 IDoes not solve the cross-reference issue but does replace some ugly handrolled selector code of mine. Thanks!
Continued ContributorAs 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
Got you. API 2.1.0 removed use internal interfaces to make API lighter.
Ignat Vilesov,
Software Engineer
Microsoft Power BI Custom Visuals
- 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!
- rdegr
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) => { - jppp
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
- rdegr
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.
- v-viig
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 [email protected]
Otherwise, you might get unexpected breaks.
Ignat Vilesov,
Software Engineer
Microsoft Power BI Custom Visuals
- rdegr
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.
