How to filter a table based on selected slicer result.

[Guli]

I have two tables like below:

Table A:

Table B:

I want to relate these two tables, but since their relationship is many to many, using Related function is not useful.

My objective results are:
when select ID=1:
Table A will be like:

  

I wish I could dynamically create this type of table A, having matched value from Table B based on the slicer ID.
What I did is to create a bridge table, Temp, using the expression of 

Temp= FILTER('Table B','Table B'[ID]="1"), this can help me create a temp table with only ID=1 case, and then using the expression of 

matchkey _withTableB=

CONCATENATEX('Temp',IF(SEARCH(FIRSTNONBLANK('Table B'[Filmtype],1),Table A[Film],,999) <>999,'Table B'[Filmtype],""))
it worked.

However, when I wrote the temp table as :
Temp=FILTER('Table B','Table B'[ID]=convert(selectedvalue (Table B[ID]),string)), aim to set the filtered condition ID as a result of slicer,  it does not work.

Any suggestions would be wonderful, thanks!

[v-yingjl · Accepted solution]

Hi @Guli ,

You can set the many-to-many relationships status as inactive and create this measure to achieve it:

matchkey_with_TableB = 
IF (
    ISFILTERED ( 'Table B'[ID] ),
    CALCULATE (
        MAX ( 'Table B'[Filmtype] ),
        USERELATIONSHIP ( 'Table A'[Film], 'Table B'[Filmtype] )
    )
)

Attached sample file that hopes to help you: How to filter a table based on selected slicer result.pbix

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[v-yingjl · Accepted solution]

Hi @Guli ,

You can set the many-to-many relationships status as inactive and create this measure to achieve it:

matchkey_with_TableB = 
IF (
    ISFILTERED ( 'Table B'[ID] ),
    CALCULATE (
        MAX ( 'Table B'[Filmtype] ),
        USERELATIONSHIP ( 'Table A'[Film], 'Table B'[Filmtype] )
    )
)

Attached sample file that hopes to help you: How to filter a table based on selected slicer result.pbix

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Guli]

@v-yingjl , it would be wonderful if you could have a good at this question:) After match these two tables, I need to have a match indicator, to see whether the value in these two columns is the same. I used the following DAX, I was expecting to see 1 when Film=matchkey_with_TableB, but it did not work. Do you have any solution for this? Thank you.

[Guli]

@v-yingjl , the DAX is 

indicater = IF('Table A'[Film]='Table A'[matchkey_with_TableB],1,BLANK())

[v-yingjl · Accepted solution]

Hi @Guli ,

You can create this measure:

indicator = 
IF(
    SELECTEDVALUE('Table A'[Film]) = 'Table A'[matchkey_with_TableB],
    1,BLANK()
)

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Guli]

@v-yingjl Thank you!!

[Guli]

Thank you so much@Yingjie, this solution solved my problem!

[Greg_Deckler]

@Guli - Put a bridge table in  between them. Use the Bridge table in your slicer. A bridge table just has a distinct list of values from your tables. You can construct one like this:

Table = DISTINCT(UNION(SELECTCOLUMNS('Table1',"SomeName",[Column1]),SELECTCOLUMNS('Table2',"SomeName",[Column2])))

Make sure "SomeName" is the same for both.

[Guli]

Thank you@Greg_Deckler.  The result is not quite what I want, it would work if I can figure out how to dynamically create a table(Or say, dynamically filter a table).

I want to filter table A as table B, based on the condition when Table A[column 1] is the result of a slicer(I will use column 1 as slicer). If I specifically point out

Table B=FILTER('Table A','Table A '[column 1]="1"), it will be a table that filtered from Table A with column 1=1,

what should I do to make this dynamic? I don't want to point out,' Table A '[column 1]=something every time, instead, using,'Table A '[column 1]=selectedvalue(Table[column 1]), but something this does not work. Do you have any suggestions?

Thank you!

[Greg_Deckler]

@Guli - If you don't want to use a bridge table, and I'm not sure I understand why you are opposed to this, you could potentially use a Complex Selector - https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Complex-Selector/m-p/1116633#M534