Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Custom filter for a calculated measure

Hi all.

I have a custom measure which obtains a value via LOOKUPVALUE. It basically compares values in a column is table A with those in table be. When it finds a match (a string) it returns the value.

I added this measure to a table visual and it works fine.

But, since this is a calculated measure, I cannot filter by it.

I mean, I can by typing one of the falues into the filter on the left sidebar (Filters) and then clicking 'Apply Filter''. This works, but I'd prefer to have a list of those values to pick from, like when you are filtering on a column.

Is there a way I can write a filter which I could drop on the canvas and define options for it?

 

Trouble is, both table A and B are remote data sources connected via direct query and I cannot add columns to either...

Please help...

6 Replies

  • some_bih's avatar
    some_bih
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi Anonymous did you try to put "measure" into calculated columns or create calculated table with measure definition.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      I created a new measure that compares values from 2 columns in 2 discreet tables, using a common key, via LOOKUPVALUE.

      My measure is: 

      Measure Name = LOOKUPVALUE('Table A'[Column containing values I want to search among],'Table A'[Searched column], ('Table B'[List of keys to use for the search]).
      I then put Measure Name in a table visual, which created a column in that visual called Measure Name. In this visual I have a bunch of columns I dragged from the other tables in the model.
      Now, whenever Measure Name finds a match between a value in Table A and one of the keys in Table B, it returns that value in my table visual under Measure Name.
  • ERD's avatar
    ERD
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Anonymous , create a separate table with possible values with no connections. Use it for the slicer and change your measure accordingly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      But how? I don't seem to be able to bring over columns from those 2 tables (say, Table A and B) into a new table.

      I can create a table alright, but I can only bring in columns from one of those tables (I tried vie SUMMARIZE()).

      For clarification, Table A is in DirectQuery mode, and Table B is in import mode - i'm not sure whethter that has anything to do with the issue I'm facing?

      TO create a new table was the first thing that came to mind but it's simply not working for me...