Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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.
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.
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.