topic Re: Create mesure when a field of one column is equal to a field of another column from another table in DAX Commands and Tips

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

Chrisjr — Thu, 04 May 2023 07:55:45 GMT

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

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

johnt75 — Thu, 04 May 2023 08:21:03 GMT

Try

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

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

Chrisjr — Thu, 04 May 2023 09:49:42 GMT

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?

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

johnt75 — Thu, 04 May 2023 10:14:10 GMT

Try

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

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

Chrisjr — Thu, 04 May 2023 10:06:13 GMT

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.

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

johnt75 — Thu, 04 May 2023 10:14:40 GMT

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

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

Chrisjr — Thu, 04 May 2023 10:22:58 GMT

many thanks johnt75

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

Chrisjr — Thu, 04 May 2023 10:26:51 GMT

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

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

johnt75 — Thu, 04 May 2023 10:32:42 GMT

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.