Slicer to filter several columns

[Anonymous]

Hi,

I've been reading several posts on internet but nothing so far that I could make to work... thanks in advance for the help.

I want to create a slicer to filter the Research Topics from two columns (text.2, text.3). In my table I have 5 rellevant columns: 1 listing all projects (abstract.text), 2 containing the text I want to filter/Research Topics (text.2, text.3), and 2 columns with the ID associated to each research topic (id.2, id.3). The below table shows several rows for each project, that's because there are more columns not showing here.

I managed to create a table (Research.topics) with two columns containing unique ID (from id.2 and id.3) and the Research Topic (text.2, text.3) named: research.topics[researchtopicid], research.topics[research.topic].

Now, my idea was to create a relationship between id.2 + research.topics[researchtopicid] and id.3 + research.topics[researchtopicid] so when I click "Research.topic[research.topic]" in the slicer, it will filter projects containing the selected research topic either in "Text.2" or "Text.3". However, only one relation can exist.

And this is what i would like to get somehow: when I click a research topic, the table will filter project where the research topic is in text.2 or text.3.

I've seen some examples such unpivoting or using the Query Editor. However, I would like to use DAX formulas to make this work.

Thanks!

[Anonymous]

Can you post a screenshot of your model?
Looks like something that can be solved using a bridge table

[Anonymous · Accepted solution]

ok now it's very clear.

First of all, as a general recommendation: your model has many bidirectional filters. It's a "bad" design decision that impacts performances and makes your measures sometimes difficult to debug and build. Sometimes you are forced to do so, and that's why I wrote "bad" and not BAD, but keep that in mind.

What you have to do is a bridge table as you cannot filter by "is either here or there".

Create (using PowerQuery) a table that contains one row per each ProjectId and both of the Id's. So something like
PROJECT_A | ID2
PROJECT_A | ID3 
PROJECT_B | ID2
PROJECT_B | ID3
etc...

To do so create a clone of your original table, remove ALL columns except ProjectId, Id2 and Id3 and then Unpivot columns id2 and id3. You should have that table. Let's call this table BRIDGE

Then create a table that contains all *unique* values of Id2+id3 (and maybe your id ResearchTexts). Call this table IDTABLE

now create these relationships
One way between your IDTABLE to BRIDGE, linking your IDs columns
Two way between your BRIDGE and your original PROJECT table, linking the Project ids

What happens now is that when you select one topic id in your IDTOPIC , this will filter all rows in your bridge that has that value in either 2 or 3 and will select all of the TOPICS, and this will then filter again the projeccts table.

Hope this helps.

[Anonymous]

Thanks so much! it worked!

Now I have to find out why when I tick one resear topic I get projects that don't have that ID. I need time to dig in. But, I learnt how to design the slice. thanks again!

[Anonymous]

Thanks. I've edited the first post. Hope this helps.