Create mesure when a field of one column is equal to a field of another column from another table

[Chrisjr]

Hello Community, 

In my model, I have 2 dimension tables and one fact table. 

The first dimension table includes only my brands. 

Brand ID	BRand	Mode
1	Audi	A1
2	Audi	A3

The second dimension table includes my competitors brands and also my brands. 

Competitor ID	Competitor Brand	Competitor Model
1	Skoda	Fabia
2	Audi	A3
3	Peugeot	203
4	PEugeot	205
5	Audi	A1
6	PEugeot	2008

The data model looks like below: 

I would like to create a Dax that would say: calculate the "Promo"  only when model in the "My Brand" dim table is equal to the model in the "My Competitor" dim table.

What dax formula could I use? 

Thank you

[johnt75]

Try

Promo same brands =
SUMX (
    'Fact table',
    IF (
        RELATED ( 'My brands'[Brand] ) = RELATED ( 'My competitors'[Brand] ),
        'Fact table'[Promo]
    )
)

[Chrisjr]

HI @johnt75 ,

Thanks for your reply.

I have tried to adapt your Dax to my need :  In my fact, there is also a column to identify the type of promo. 

For exemple, I want to calculate the Promo for the "Rebate" type. 

I tried the following but obviously is inccorrect: 

SUMX (
    'Fact table',
    IF (
        RELATED ( 'My brands'[Brand] ) = RELATED ( 'My competitors'[Brand] ), Filter( lookup_promotype[Promo Type] = "Rebate"),
        'Fact table'[Promo]
    )
)

Or is it possible to use the Related function with Calculte? 

[johnt75]

Try

SUMX (
    'Fact table',
    IF (
        RELATED ( 'My brands'[Brand] ) = RELATED ( 'My competitors'[Brand] ) && RELATED( lookup_promotype[Promo Type]) = "Rebate",
        'Fact table'[Promo]
    )
)

[Chrisjr]

@johnt75 I get an error message : Function RELATED expects a fully qualified column reference as its argument.

the part 

RELATED( lookup_promotype[Promo Type] = "Rebate"

 seems to be the issue. 

[johnt75 · Accepted solution]

My fault, I had a ) in the wrong place. I've edited my post to correct it.

[Chrisjr]

@johnt75 Question by curiosity: in this case, what would be more efficient, using Calulate(sum()) or using Sumx like you did? 

[johnt75]

I don't think you could do it without SUMX, as you need to get a row context to compare the values from my brands and competitors.

You could wrap that SUMX in a CALCULATE to perform the filtering on promo type, not sure which would be more efficient, you'd need to do tests using DAX Studio, but unless it was a very large dataset I would think the difference would be negligible.

[Chrisjr]

many thanks @johnt75