Use an inactive filter to filter the fact table

[Meena0155]

Hi all,

I have a data model as shown in the attachment.

1. Date Master and Division are related based on the common field PK

2. Date Master and Date Breakdown are related based on the common field PK

3.Date Breakdown and Division are related (inactive) as Date Breakdown(alt_pk)=Division(pk)

I want to create a table with Date Start and Accounts from division table but the not using the relation 1 described above but by using relationship 2 and 3.

I created a measure called Accounts New as 

CALCULATE(sum(Divison[Accounts]),CROSSFILTER('Date Master (2)'[PK],Divison[PK],None),USERELATIONSHIP('Date Breakdown'[alt_pk],Divison[PK])).

Now when I created the table with Date Start and Accounts New, I can see that table Division is filtered through relation 2 and relation 3. 

I know it sounds strange, but I am not abe to explain how its working. I understand that CROSSFILTER('Date Master (2)'[PK],Divison[PK],None) will only work on metrics but I am surprised how the Date Start while being used with  Accounts New uses the relationships 1 and 2 

Please help. 

Regards,

Meena

[Meena0155]

Thanks a lot @bhanu_gautam 

So when I add a table visual (or any visual for that matter) with dimensions and measures from a source table(say division table based on example above), the values of dimensions shown in the table visual is based on what the measures that we are using. Is that a right statement?

Regards,

Meena 

[Anonymous]

Hi @Meena0155 

Your statement is correct in the sense that the values of dimensions shown in the visual can be influenced by the measures if those measures modify the filter context. However, the dimensions themselves are not directly calculated by the measures, they are simply filtered by the context.

Best Regards,

Jayleny

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

[bhanu_gautam]

@Meena0155 DAX
Accounts New =
CALCULATE(
SUM(Division[Accounts]),
CROSSFILTER('Date Master'[PK], Division[PK], NONE),
USERELATIONSHIP('Date Breakdown'[alt_pk], Division[PK])
)

CROSSFILTER('Date Master'[PK], Division[PK], NONE): This deactivates the active relationship between Date Master and Division.
USERELATIONSHIP('Date Breakdown'[alt_pk], Division[PK]): This activates the inactive relationship between Date Breakdown and Division for the calculation.

When you create a table visual with Date Start from the Date Master table and Accounts New measure, Power BI will use the relationships as specified in the measure. The CROSSFILTER function ensures that the direct relationship between Date Master and Division is ignored, while the USERELATIONSHIP function activates the relationship between Date Breakdown and Division.