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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DJBrennan
Frequent Visitor

DAX RELATED not working for me

Hi,
i have a fact table which contains many repetitions of a Supply Number with different usage values by date. I have a dim table that contains same supply number and various prices. I want to create a measure that multiplies usage by RElated price.
I can set out a table that lists supply number from fact table and correctly lists the various price elements from theDim table.

however when I try to write a measure
cost = sum(fact[usage])* RELATED(dim[price] it doesn't work. I am advised that the related table can't be found.

any advice gratefully received
Denis

2 ACCEPTED SOLUTIONS
OwenAuger
Super User
Super User

@DJBrennan

 

I would iterate over the values of the dim[price] column or the dim table using one of these two measures:

cost =
SUMX ( VALUES ( dim[price] ), dim[price] * CALCULATE ( SUM ( fact[usage] ) ) )

 or

cost =
SUMX ( dim, dim[price] * CALCULATE ( SUM ( fact[usage] ) ) )

 The red part can be replaced with a measure if you have already defined it.


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

@DJBrennan

 

In this scenario, since the multiply need to be done on each row level, you need to multiple the RELATED price in SUMX() function.

 

I assume you have table like below:

 

5.PNG

 

You just need to use SUMX() function.

 

Total Cost = SUMX('Fact','Fact'[Usage]*RELATED(Supply[Price]))

6.PNG

 

 

Regards,

View solution in original post

4 REPLIES 4
OwenAuger
Super User
Super User

@DJBrennan

 

I would iterate over the values of the dim[price] column or the dim table using one of these two measures:

cost =
SUMX ( VALUES ( dim[price] ), dim[price] * CALCULATE ( SUM ( fact[usage] ) ) )

 or

cost =
SUMX ( dim, dim[price] * CALCULATE ( SUM ( fact[usage] ) ) )

 The red part can be replaced with a measure if you have already defined it.


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
Phil_Seamark
Employee
Employee

Have you set up a relationship between the two tables?

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Yes, of course. that's why the table function perfectly. They're related on Supply Number

de ks

@DJBrennan

 

In this scenario, since the multiply need to be done on each row level, you need to multiple the RELATED price in SUMX() function.

 

I assume you have table like below:

 

5.PNG

 

You just need to use SUMX() function.

 

Total Cost = SUMX('Fact','Fact'[Usage]*RELATED(Supply[Price]))

6.PNG

 

 

Regards,

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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