Custom Search Visual

[jijods4]

I am trying to create a custom search filter.

Say I have 3 category Category, Country and Segment.

Category has values Furniture, Office Supplies, Technology

Country has values  (India,Canada,USA) and

Segment has values Consumer, Home Office, Corporate

And in the search box I can type any of the above values and all charts has to respond to that value.

I tries Implimenting but I get 27 items (3*3*3) since they are aggregating.

Actually WHat I expect is 9 items without any aggreagation.

Any Idea how to do this??

[v-evelk]

I am not sure that completely understood your issue but it looks logically correct that you get 27 items because if you use field in a category bucket, Power BI will group rows by this field so, if you use 2 fileds in 1 categorical bucket you will get grouped data by combination of these fields.

So, grouping of 3 categorical fields will return only uniq combinations and number of such combination will be 9 to cover only 1 country (for instance), for 3 countries it will be 27.

If I missed something please will provide more details or/and data regarding your issue.

Kind Regards,

Evgenii Elkin,
Software Engineer
Microsoft Power BI Custom Visuals
pbicvsupport@microsoft.com

[jijods4]

Thanks for the reply.

So since it would give the 27 unique items, which will not solve my problem, I have taken the following approach.

Since my idea is to filter for any item say, i want to filter based on country name , then I type the country name , it auto fills the relevant country and once I click the entire page gets filtered for that country. Then If I want to filter based on Segment value say, Consumer Once I type (Con) it auto fills to Consumer and once I click chart gets filtered.

I was able to acheive it. What I tried is Once I get the 27 unique items I have written a js code to remove the duplicates so I get only 9 unique items.(from 27 to 9). Then I use the powerbi-models to filter it. Like when user click on Consumer(a value of Segement) onclick I have written that filter it based on Segment. You can see it in the Following GIF.

As you can see above I have two items in category bucket and I am able to make the functionality. BUt I issue what I am facing now is I need multi filter say if I want to filter one item from category and another from segment I am not able to do it. I know powerbi has a filter api and current;ly i am using basic. It has something called Tuple API.

Once I add it in my code the code stops. as in no error but nothing is happening. Meaning I have wriiten a console.log() above the below code and after it..

let target: ITupleFilterTarget = [
      {
          table: "_Sales Target",
          column: "Category"
      },
      {
          table: "_Sales Target",
          column: "Segment"
      }
  ];
let values = [
      [
          // the 1st column combination value (aka column tuple/vector value) that the filter will pass through
          {
              value: "Technology" // the value for `Category` column of `_Sales Target` table
          },
          {
              value: 'Corporate' // the value for `Segment` column of `_Sales Target` table
          }
      ],
      [
          // the 2nd column combination value (aka column tuple/vector value) that the filter will pass through
          {
              value: "Furniture" // the value for `Category` column of `_Sales Target`` table
          },
          {
              value: 'Consumer' // the value for `Segment` column of `_Sales Target` table
          }
      ]
  ];
console.log('before filter');
let filter: ITupleFilter = {
      $schema: "http://powerbi.com/product/schema#tuple",
      filterType: FilterType.Tuple,
      operator: "In",
      target: target,
      values: values
  }
console.log('after filter');
host.applyJsonFilter(filter, "general", "filter", FilterAction.merge);

Once I run it I see the before filter message and not after filter message. So it means there is something that i am missing or the way i am doing is not correct..

If you could help me out resolving this it would be so helpful. I tried contacting pbicvsupport@microsoft.com but I did not receive any updates. Hoping you would be able to help me in this.

Thanks in Advance. 

[jijods4]

Do let me know if I need to share the full source code as well, I will be happy to share the same.

[v-evelk]

If it possible please will do.

Thanks!

Evgenii Elkin,
Software Engineer
Microsoft Power BI Custom Visuals
pbicvsupport@microsoft.com

[jijods4]

Hi ,

I have created a git repo and sharing you the same. myfiltervisual 

Thanks.

[v-evelk · Accepted solution]

Hi,

Check the console and you will see that filterType is undefined.

It happens because constant FilterType.Tuple is unavailable -- it doesn't contain export declaration.

You need to use it like the following:

filterType: 6

Kind Regards,

Evgenii Elkin,
Software Engineer
Microsoft Power BI Custom Visuals
pbicvsupport@microsoft.com

[ybd1]

Hi evelk 🙂

I have a simomiller problem....

i cant apply the pbi js "setSlicerState()" function with the correct filter syntax.

when i run this on console i get :Uncaught SyntaxError: Unexpected token ':'

let filter: ITupleFilter = {
$schema: "http://powerbi.com/product/schema#tuple",
filterType: FilterType.Tuple,
operator: "In",
target: target,
values: values
}

I was trying to constraact the filter this way:

slicerFilter = {

$schema: "http://powerbi.com/product/schema#tuple",

target: ITupleFilterTarget = [{table: "d1", column: "G1"},{table: "D1", column: "De"}],

filterType: 6,

operator: "In",

values: [[{value: "adam"},{value: "josef"}]]

};

i later invoke it with the setslicerstate funcxtion:

...
slicer.setSlicerState({ filters: [slicerFilter] })
...

but got a "filters property is invalid" error

[jijods4]

Hi ,

Thanks for the help. Let me know if there is any doc where I can find other values of the FilterTypes and their usage. 

now the error has gone and it is working as I exepected.

and there was one more problem has occured. When I speified the column name one column has been miss spelled, so the entire filter has not happened. Is there any mechanism that if the column names or table names are miss spelled , an exception would thrown stating unknown column/table name found. or say if the value is not availble in the category no value exists. like so.

just asked out of curiosity.

otherwise if you could tell how to debug would be helpful. I have found this Link. But I don't know where to put the below code.

module powerbi.extensibility.visual {
    export function logExceptions(): MethodDecorator {
        return function (target: Object, propertyKey: string, descriptor: TypedPropertyDescriptor<any>)
            : TypedPropertyDescriptor<any> {

            return {
                value: function () {
                    try {
                        return descriptor.value.apply(this, arguments);
                    } catch (e) {
                        console.error(e);
                        throw e;
                    }
                }
            }
        }
    }
}

Regards,

Jijo.

[v-evelk]

Hi Jijo,

I am not sure that there is other information regarding using of filters, sorry.

I am also unsure regarding exception if column name in filtration is wrong, but try to wrap applyJsonFilter into try ... catch and throw an error in the catch block.

Regarding the code that you provided in the message, you can put this function-decorator at the same file as your visual class (outside of the class) and then don't forget to add this decorator to target method like below:

@logExceptions()
public update(options: VisualUpdateOptions) {

Kind Regards,

Evgenii Elkin,
Software Engineer
Microsoft Power BI Custom Visuals
pbicvsupport@microsoft.com