Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Dear Community,
I'm developing a little custom visual that consists of two dropdowns.
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