Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Chrisjr
Helper IV
Helper IV

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

Hello Community, 

 

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

 

The first dimension table includes only my brands. 

 

Brand IDBRandMode
1AudiA1
2AudiA3

 

 

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

 

Competitor IDCompetitor BrandCompetitor Model
1SkodaFabia
2AudiA3
3Peugeot203
4PEugeot205
5AudiA1
6PEugeot2008

 

 

 

The data model looks like below: 

Chrisjr_0-1683186258005.png

 

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

1 ACCEPTED SOLUTION

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

View solution in original post

8 REPLIES 8
johnt75
Super User
Super User

Try

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

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? 

Try

 

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

 

@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. 

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

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

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.

many thanks @johnt75 

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.