Construct Filter with Javascript function

[Coltsbro]

Hi all,

I am currently working on bringing Power BI Embedded to a place that our company can think of using this more often. One limitation I can currently see is potentially needing different filters/filter styles from page to page. Rather than constantly type out the code for the filter, I thought I would build a function to make them based off of inputted parameters.

EX: I have a filter: 

            var useFilter = {
                $schema: "http://powerbi.com/product/schema#advanced",
                target: {
                    table: table,
                    column: column
                },
                logicalOperator: 'And',
                conditions: [
                    {
                        operator: "Is",
                        value: value
                    }
                ]
            };

Where table, column, and value were some hard coded variables I had in the script section. Now, this is fine for a one off, but isn't dynamic enough for what we might need. So I created a function to try and return this object, based off of inputted parameters. Here is the function:

function advancedFilter(table, column, logicalOperator, operator, value){
    
    if (logicalOperator === undefined) {
        logicalOperator = "And";
    }
    if (operator === undefined) {
        operator = "Is";
    }
    if (value === undefined) {
        value = 0;
    }

    var filter = {
        $schema: "http://powerbi.com/product/schema#advanced",
        target: {
            table: table,
            column: column
        },
        logicalOperator: logicalOperator,
        conditions: [
            {
                operator: operator,
                value: value
            }
        ]
    };
}

I would then set a variable like so:

var useFilter = advancedFilter(table, column, "And", "Is", arr)

Where arr is a basic array variable with a value (104 for example) in it. I have also tried just passing the raw value instead of an array. What am I doing wrong here? Every time I try to use this, I get an error: Uncaught (in promise) [Array[4]]. If I use the example filter at the top, without the function, everything works fine. Anyone have answers?

[Eric_Zhang · Accepted solution]

@Coltsbro

Below code works in my test. You can debug your filter by using the alert statement before applying it. Check more details on Filters. By the way, do note that when using "In", it is values and when other operators it is a scalar value. You may have to put more logics in your function.

var arr = [1,2,3,4,6,8]

function advancedFilter(table, column, logicalOperator, operator, value){
    
    if (logicalOperator === undefined) {
        logicalOperator = "And";
    }
    if (operator === undefined) {
        operator = "Is";
    }
    if (value === undefined) {
        value = 0;
    }

    return filter = {
        $schema: "http://powerbi.com/product/schema#advanced",
        target: {
            table: table,
            column: column
        },
        //logicalOperator: logicalOperator,
        //conditions: [
        //    {
                operator: operator,
                //It is "values" when using a operator "In"
                values: value
        //    }
        //]
    };
}


alert(JSON.stringify(advancedFilter("table","column","","In",arr)));

[Coltsbro]

This is now working for operators besides "In". Modified code: 

function advancedFilter(table, column, logicalOperator, operator, value){
    
 if (logicalOperator == undefined || logicalOperator == "" || logicalOperator == null) {
 logicalOperator = "And";
 }
 if (operator == undefined || operator == "" || operator == null) {
 operator = "Is";
 }
 if (value == undefined || value == "" || value == null) {
 value = 0;
 }

    if (operator == "In") {
        return filter = {
            $schema: "http://powerbi.com/product/schema#advanced",
            target: {
                table: table,
                column: column
            },
            logicalOperator: logicalOperator,
            conditions: [
                {
                    operator: operator,
                    values: value
                }
            ]
        };
    }
    else {
        return filter = {
            $schema: "http://powerbi.com/product/schema#advanced",
            target: {
                table: table,
                column: column
            },
            logicalOperator: logicalOperator,
            conditions: [
                {
                    operator: operator,
                    value: value
                }
            ]
        };
    }
    
}

Called in the view: 

var arr = [104, 102, 117];
var useFilter = advancedFilter(table, column, "", "In", arr);

Pushed to the report filters:

                report.on('loaded', event => {
                    report.getFilters()
                    .then(filters => {
                        filters.push(useFilter);
                        return report.setFilters(filters);
                    })
                })

It may also be worth noting that I have the function in a separate javascript file that I am referencing in the view. Here is what returns in the alert text box: 

{  
   "$schema":"http://powerbi.com/product/schema#advanced",
   "target":{  
      "table":"SiteInfo",
      "column":"SiteId"
   },
   "logicalOperator":"And",
   "conditions":[  
      {  
         "operator":"In",
         "values":[  
            104,
            102,
            117
         ]
      }
   ]
}

I still get the error message in the developer console on the web page: Uncaught (in promise): Array[4].  

Array[4]
0
:
Object
message
:
"operator is invalid. Not meeting required constraint"
__proto__
:
Object
1
:
Object
message
:
"values is invalid. Not meeting required constraint"
__proto__
:
Object
2
:
Object
message
:
"conditions.0.value is invalid. Not meeting required constraint"
__proto__
:
Object
3
:
Object
message
:
"filter is invalid"

What do you suggest?

[Coltsbro]

Disregard. It appears that "In" is specific to a basic filter, which is why I was throwing an error. All seems to be working perfectly now, than you for your help!

[Eric_Zhang]

@Coltsbro

You're always welcome.