Dear Community,
I'm developing a little custom visual that consists of two dropdowns.
- Problem 1: Currently my dropdowns are successfully populated with data and I can select values. I want to add filtering functionality to the dropdowns.
As you can see in code, I have tried to implement it myself but I can't make it work as intended. Any selections in the first dropdown don't do anything. Selections in the second dropdown filter data in the page to selected value but don't affect avalible options in the first dropdown.
- Problem 2: I want to create little eraser icons near both dropdowns. Clicking them clears selection and removes filters in corresponding dropdown.
Logic is implemented in visual.ts :
import powerbi from "powerbi-visuals-api";
import "./../style/visual.less";
import VisualConstructorOptions = powerbi.extensibility.visual.VisualConstructorOptions;
import VisualUpdateOptions = powerbi.extensibility.visual.VisualUpdateOptions;
import IVisual = powerbi.extensibility.visual.IVisual;
import DataView = powerbi.DataView;
import * as d3 from "d3";
import { BasicFilter, IFilterColumnTarget } from "powerbi-models";
import IFilter = powerbi.IFilter;
import FilterAction = powerbi.FilterAction;
export class AALCalculatorVisual implements IVisual {
// Visual elements
private svgRoot: d3.Selection<SVGElement, {}, HTMLElement, any>;
private dropdowns: d3.Selection<HTMLSelectElement, {}, HTMLElement, any>[] = [];
// Parameters
private visualHost: powerbi.extensibility.visual.IVisualHost;
private currentDataView: DataView | null = null;
private postalCode: string = "";
private lob: string = "";
constructor(options: VisualConstructorOptions) {
this.visualHost = options.host;
this.svgRoot = d3.select(options.element).append("svg")
.style("background", "rgb(0, 82, 164)");
// Dropdowns (moved above input fields)
const dropdownNames = ["Postal Code", "LOB"];
dropdownNames.forEach((name, index) => {
this.svgRoot.append("text")
.text(name + ":")
.attr("x", 10)
.attr("y", 80 + index * 40)
.style("font-size", "16px")
.style("fill", "white");
const dropdown = d3.select(options.element)
.append("select")
.attr("class", "dropdown-box")
.style("position", "absolute")
.style("top", `${70 + index * 40}px`)
.style("left", "150px")
.style("width", "130px")
.style("padding", "8px")
.style("border", "1px solid rgb(0, 122, 197)")
.style("border-radius", "6px")
.style("background", "white")
.style("color", "black")
.on("change", (event) => this.handleDropdownChange(index, event));
this.dropdowns.push(dropdown);
});
}
private handleDropdownChange(index: number, event: any): void {
const value = event.target.value;
if (index === 0) {
this.postalCode = value;
} else if (index === 1) {
this.lob = value;
}
if (this.currentDataView) {
this.applyFilter(this.currentDataView);
}
}
public update(options: VisualUpdateOptions) {
this.currentDataView = options.dataViews[0]; // Save the DataView to a class property
this.svgRoot
.attr("width", options.viewport.width)
.attr("height", options.viewport.height);
let dataView: DataView = this.currentDataView;
let table = dataView.table;
let postalCodeIndex = table.columns.findIndex(col => col.roles["PostalCode"]);
let lobIndex = table.columns.findIndex(col => col.roles["LOB"]);
if (postalCodeIndex !== -1) {
let postalCodes = Array.from(new Set(table.rows.map(row => String(row[postalCodeIndex]))));
this.populateDropdown(this.dropdowns[0], postalCodes, this.postalCode);
}
if (lobIndex !== -1) {
let lobs = Array.from(new Set(table.rows.map(row => String(row[lobIndex]))));
this.populateDropdown(this.dropdowns[1], lobs, this.lob);
}
console.log("DataView Columns: ", dataView.metadata.columns);
}
private populateDropdown(dropdown: d3.Selection<HTMLSelectElement, {}, HTMLElement, any>, values: string[], selectedValue: string): void {
dropdown.selectAll("option").remove(); // Clear existing options
// Preserve existing selection
let isSelectedValueInList = values.includes(selectedValue);
// Add options dynamically
values.forEach(value => {
dropdown.append("option")
.text(value)
.attr("value", value)
.attr("selected", value === selectedValue ? "selected" : null); // Keep previous selection
});
if (!isSelectedValueInList) {
dropdown.property("value", ""); // Reset only if the selection is invalid
}
}
private applyFilter(dataView: DataView): void {
if (!this.visualHost) {
console.error("Visual Host is not initialized properly.");
return;
}
const filterValues: string[] = [];
let targets: IFilterColumnTarget[] = [];
if (this.postalCode) filterValues.push(this.postalCode);
if (this.lob) filterValues.push(this.lob);
if (dataView?.metadata?.columns) {
dataView.metadata.columns.forEach(column => {
console.log("Checking Column:", column.displayName, "Query Name:", column.queryName);
if (column.displayName === "Postal Code" && this.postalCode) {
targets.push({
table: column.queryName.split('.')[0],
column: column.displayName
});
}
if (column.displayName === "LOB" && this.lob) {
targets.push({
table: column.queryName.split('.')[0],
column: column.displayName
});
}
});
}
console.log("Targets:", targets);
console.log("Filter Values:", filterValues);
if (targets.length > 0 && filterValues.length > 0) {
const filters: IFilter[] = targets.map((target, index) =>
new BasicFilter(target, "In", [filterValues[index]]) // Use index instead of shift()
);
filters.forEach(filter => {
this.visualHost.applyJsonFilter(filter, "general", "filter", FilterAction.merge);
});
console.log("Filters Applied: ", filters);
} else {
this.visualHost.applyJsonFilter(null, "general", "filter", FilterAction.merge);
console.log("Filters Cleared.");
}
}
}
Here is my capabilities.json:
{
"dataRoles": [
{
"name": "PostalCode",
"kind": "Grouping",
"displayName": "Postal Code"
},
{
"name": "LOB",
"kind": "Grouping",
"displayName": "LOB"
}
],
"dataViewMappings": [
{
"table": {
"rows": {
"select": [
{ "for": { "in": "PostalCode" }, "dataReductionAlgorithm": { "singleCategory": {} } },
{ "for": { "in": "LOB" }, "dataReductionAlgorithm": { "singleCategory": {} } }
]
}
},
"conditions": [
{
"PostalCode": { "max": 1, "min": 0 },
"LOB": { "max": 1, "min": 0 }
}
]
}
],
"objects": {
"general": {
"displayName": "General",
"properties": {
"filter": {
"type": {
"filter": true
}
}
}
}
},
"privileges": []
}
Thank you very much in advance!
Kind regards,
Artem
