Custom filter for a calculated measure

[Anonymous]

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...

[ERD]

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

[Anonymous]

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... 

[some_bih]

Hi @Anonymous without some information about your model, I do not see how to suport you. Maybe somebody else have some ideas.

[ERD]

Please, provide some demo. Use this for reference: https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-Get-Your-Question-Answered-Qu...

[some_bih]

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

[Anonymous]

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.