Or slicer filter with many to many relationship

[EduardoCruz]

Hello all,

I have earlier asked a question to solve a similar issue in dax, but I have found a roadblock in my dax development.

I was trying to emulate the following SQL union query in Power BI. I tried to use or filter logic, to get the desired results, but so happens that the second query is joining a table which has a many-to-many relationship (InterestCategory with FactActiveOpportunities).

SELECT
a.*
FROM [DM-Datavault].[dbo].factactiveopportunities a
LEFT JOIN [DM-Datavault].[dbo].DimCompany b on a.dimcompanysupplier = b.dimcompanycode
WHERE b.dimcompanycode = '1'
UNION
SELECT
a.*
FROM [DM-Datavault].[dbo].factactiveopportunities a
WHERE DimCategoryCode in
( SELECT
*
FROM InterestCategoryOwner
WHERE DimCompanyCode = '1')

Here's a picture of the model I'm trying to implementHow can I tackle this issue? I'm always getting stuck on creating the measure.

[v-chuncz-msft]

@EduardoCruz ,

You may take a look at links below.

• https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/
• https://www.sqlbi.com/articles/from-sql-to-dax-in-and-exists/

[EduardoCruz]

Hi @v-chuncz-msft Sam Zha

Thank you for your reply.

I'm sorry, but I'm still feeling a bit lost. I've read and tried to apply the logic from the links you passed me, but I still didn't manage to overcome this problem. I need to add OR logic to a master slicer visual where I pass the same code onto 2 parameters.

The first filters the fact table on one to many values, while the second one filters based on a subquery in the where clause (due to a many to many relationship with the fact table.

The thing is that I want to apply this logic for the business user to filter the report as he pleases.

I'm having a hard time applying this filter.

I've tried using Calculated tables, with contains, but it's unresponsive to slicer filters the way I need it to be.

Is there an alternative? I'm still relatively new to dax so perhaps I may have not tested all the possible solutions.

Thanks,

Eduardo