Fix Filtering | Custom Visual
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
TopN + Others based on selected categories from filter feature
Hello, fellow PoweBI enthusiast, I have a question that I'm hoping someone could help me with. I have a table containing credit categories (all is "Total Kredit"), subcategories (sub_kategori), dates, and bu. The "bu" column represents the value amount of credit for each subcategory and date. Now, I'm attempting to create a pie chart that can be dynamically filtered by date, specifically focusing on quarters and years. Additionally, users should be able to filter the subcategories to determine the quantity and specific subcategories they wish to display. With this in mind, my goal is to present only the chosen subcategories for each date based on their bu values. For the categories that users have not selected, I intend to group them under the label "Others." The chart example (on Excel): Is that possible to do? I've watched several tutorials on YouTube and looked through the Power BI community, but the solutions I found don't quite fit my specific problem. Any assistance would be greatly appreciated. Thank you in advance!
Convert default AND to OR using Custom Visuals
I would like to create custom visuals to convert default AND to OR logical operator. I am able to do this using DAX query. https://www.sqlbi.com/articles/using-or-conditions-between-slicers-in-dax/ Do you know how I can achieve this by cresting custom visuals. Is there any existing slicer which will do for me ? Is there any way in powerbi I can execute query by passing parameters through custom visuals ? Like Entity Framework Or Ado.Net ?
2 Measures on same line graph
I have 2 rolling 14 day average calculations and Im hoping to display them (highlighted) on the same line graph: However I've tried to create a date bridge and another table to relate to and still no luck with either approach. Where it should look more like this: Can anyone help with this? SEE FILE ATTACHED https://www.dropbox.com/s/ksi9fm0kbzpj1hg/Notification%20vs.%20Epi.pbix?dl=0DAX for Custom Filter to Select Past Months & Current Month's Latest Date
https://drive.google.com/file/d/1ZBTilbeJxPISCZsqlv3FH2Qffvz09k1a/view?usp=drivesdk This is a sample of my dataset. When data is refreshed today (May 24), data will be available until the previous day (May 23). for the current month, data will be updated incrementally (day wise status). for example - for all departments, I have day wise status. (May 1, May 2, May 3, May 4..... May 31 ) At the end of the current month - May, day-wise status is deleted and only May 31 status (final for the month) is stored and the day wise status is started for June. I want to show Past months & Current Month -latest date status for monthly trend visual. Currently, I have put "Latest data" in Filters and Selecting past months and "Latest data". Issue - At the beginning of Next Month, the Filter does not include "May data" and I have to be select manually. How to achieve automating this selection so that all past months are selected automatically along with Current Month Latest date?
