Boolean slicer with multiple dimensions linked together through bridge

[gck02]

Hello,

Here is the scenario of my question:

The report is connected to an on-premise SQL Server and we're using Mixed storage mode (tables from SQL Server are in DirectQuery mode).
Users want to change measures results based on the existence (or the absence) of relationship between two dimension tables. These two dimensions are linked with a bridge table that ensures the many-to-many relationships. The goal is for them to select a yes/no option to display measures results whether there are relationships between the dimensions or not.

The first dimension, [Campaign], is the one that is connected to my fact table. The other dimension, [MarketingList], is linked to [Campaign] with [BridgeCampaignMarketingList].

Before using Power BI, we were solving this issue in SQL by joining the tables and then adding a condition "WHERE MarketingListDwhId IS NULL" (IS NOT NULL) => this condition being dynamically built with a slicer Yes/No whether there is a relationship or not.

Currently, I'm still using a SQL JOIN in Power Query to solve this issue and to display a slicer that is in the [Campaign] dimension:

    ,CAST(CASE
      WHEN b.CampaignDwhId IS NOT NULL THEN 1
      ELSE 0
     END AS bit) AS [Marketing List available]
  FROM [dbo].[Campaign] c
  LEFT JOIN [dbo].[BridgeCampaignMarketingList] b
    ON c.CampaignDwhId = b.CampaignDwhId

Which leads to the desired result:

My question is, do you think there is a way to solve this without having to add this SQL joins in Power Query but using exclusively Power BI Dax?

Thanks in advance for your help.

Regards,

N.

[v-jianboli-msft]

Hi @gck02 ,

Have you ever considered about active and inactive relationships? 

By default, active relationships propagate filters to other tables. Inactive relationship, however, only propagate filters when a DAX expression activates (uses) the relationship.

For more details, please refer to:

Active vs inactive relationship guidance - Power BI | Microsoft Learn

Hope it would help you.

Best Regards,

Jianbo Li

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

[gck02 · Accepted solution]

Hi Jianbo Li,

Thanks for your answer. Actually, on another topic I was using calculated columns with a DirectQuery model and the performance was very poor. I also tried using DAX measure but the SQL solution was far better regarding performance.

So I'll continue to use SQL directly when connecting to our SQL Server on-premise as it looks like it is the solution with least impact on performance.

Once agains thanks for your answer.

Best regards,

N.