undefi

[Sno_93]

Hi Everyone,

I have two tables: Targets and Sales. Targetsis a lookup table and contains monthly target for values, and includes columns Country and Brand. Sales is my fact table and contains all the daily sales values, and also has columns Country and Brand. I know Power BI can only allow one active relationship between the two tables. What I want to do is to create a measure that calculates value in Targets at both a Country and Brand level, and responds to both Country and Brand filters. This needs me to somehow join the tables on both of these columns. I have set the relationship for the Brand columns in the two tables to be active. I have tried USERELATIONSHIP in DAX and it is overriding the active relationship, when instead I want both relationships active. Can anyone advise? Please see sample code below:

Turnover_Target = CALCULATE(sum('Targets'[Turnover_Target])/4,  USERELATIONSHIP('Sales'[Country],'Targets'[Country]))

[cengizhanarslan]

You do not actually need two relationships here. The correct approach is TREATAS which virtually applies the filter from Sales columns onto the Targets table without needing any physical relationship beyond the one you already have.

Please try the measure below:

Turnover Target =
CALCULATE (
    SUM ( 'Targets'[Turnover_Target] ) / 4,
    TREATAS (
        SUMMARIZE ( 'Sales', 'Sales'[Country], 'Sales'[Brand] ),
        'Targets'[Country], 'Targets'[Brand]
    )
)

The TREATAS measure solves the immediate problem, but it is worth flagging that this model structure is working against best practice. The correct architecture is a star schema where Country and Brand are separate dimension tables, each with a single relationship to both your Sales fact table and your Targets fact table.

[Sno_93]

Thank you! I'll use the STAR scema going forward.

[krishnakanth240 · Accepted solution]

Hi @Sno_93 

You cannot have both Country and Brand relationships active at the same time and USERELATIONSHIP only activates one and not both. You can handle in DAX by applying both filters using TREATAS which simulates a multi column relationship.

Turnover_Target =CALCULATE(

SUM('Targets'[Turnover_Target]) / 4,

TREATAS(VALUES('Sales'[Country]), 'Targets'[Country]),TREATAS(VALUES('Sales'[Brand]), 'Targets'[Brand]))

It allows the measure to respond to both Country and Brand filters without needing multiple active relationships

[Sno_93]

Thanks a bunch

[MFelix]

Hi @Sno_93 ,

For this you need to go to the best practices in this case to use a star schema with Countries and Brands being part of two separate dimension tables that you can then make a relationship with your fact tables (Sales and the target tables) no need to have any userelationship on your model.

[Deku]

This