How to filter selection if it is contained in Row of column

[jmoko]

Hello 

I am struggling to filter data if it is contained in a row : 

in my visual : 

FILTER 

A

B

C

D

E

F

G

ROWS

1- A;C

2- A;D;E

3- F;C;D;A

4- B-G

in my dashboard , if I Select "A" , then rows 1,2,3 would be returned 

I am not sure how to do that 

[jmoko]

Hello @selimovd 

Yes the range is taken from a different table ( may change ) , ie A to G 

but these can be anywhere in the row.

[selimovd · Accepted solution]

Hey @jmoko ,

I think I could solve that with a mapping table:

The mapping table is represeting all possible combinations:

This was done with a little more complex Power Query solution. The key element there was to translate the range (e.g. B-G) into numbers (2 - 7) and then expand this range and re-translate it to the letters. This in combination with the split by semicolon and an unpivot is returning the proper mapping table.

Please check the attached solution. The result looks like that:

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic

[jmoko]

Hi @selimovd 
Thank you so much for that , that would help but the data is not static and I don;t want to go and amend the table every time there is a change . Would you know how to populate the RowsMapping table from the ROWS table ? all are separated with semi colon within the row.

[selimovd]

Hey @jmoko ,

my approach is dynamic. Where would you have an issue when the data is changing?

Best regards

Denis

[jmoko]

Hello @selimovd 
Oh , I am soo sorry , I didn't realise you did it dynamically !!   that is perfect !!!   THANK YOU SOOOO MUCH !! 

[selimovd]

Hey @jmoko ,

hehe, don't worry. I'm happy it worked eventually.

Best regards

Denis

[selimovd]

Hey @jmoko ,

there is a possibility with a mapping table.

The most difficult part is for sure the range (e.g. B-G). Before I put a lot of effort into this, the range will be single letters, like from A to G, right?

Thank you and best regards

Denis