Forum Discussion

Sno_93's avatar
Sno_93
Frequent Visitor
4 months ago
Solved

undefi

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...
  • krishnakanth240's avatar
    krishnakanth240
    4 months ago

    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

cengizhanarslan's avatar
cengizhanarslan
Super User
4 months ago

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's avatar
Sno_93
Frequent Visitor
4 months ago

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