cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Using value from another linked table in a measure

Hi Guys. I'm stuck really bad in this one. I have 2 tables : 1. Facts table 2. Dimesnion table. Following image depicts the relationship established between the 2. The table at the top is the Facts table. "OI Owrking Days" is the dimension table.

 

theDarkPrince_0-1603388074326.png

I wish to create a multiline chart which will have the "FY" field in the legend well, Month field in Axis well and a measure in the Values well. The measure is supposed to calculate Per day amount so the formula for the same would be : SUM(Amount)/working days. I need to have the working days to be fetched from dimension table for each month that is plotted on the X-Axis. Following is what I have tried so far :

Amt Per Day =
var AmtPerDay = [Total Amount] / FILTER( 'Facts Table', RELATED( 'Dimensions Table'[WORKING_DAYS] ) )
RETURN
AmtPerDay
Note : Both the tables are fetched using DirectQuery mode. 😐
Any help is appreciated. 
3 REPLIES 3
Highlighted
Super User IV
Super User IV

@theDarkPrince , Try like

Amt Per Day =
var AmtPerDay = [Total Amount] / Sumx( 'Facts Table', RELATED( 'Dimensions Table'[WORKING_DAYS] ) )
RETURN
AmtPerDay

 

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted

I'm afraid that did not work @amitchandak. Check out the image below :

 

theDarkPrince_0-1603429684361.png

 

This is how the line chart shoud look like. But it looks like :

theDarkPrince_1-1603429746804.png

 

I am not sure how to get the desired results. Been trying a lot of different functions since last 2 days but nothing has worked thus far. 😞

Highlighted

Hi @theDarkPrince ,

 

Would you please use the following measure:

 

 

Amt Per Day =
var AmtPerDay = SUMX('Facts Table', [Total Amount]/RELATED( 'Dimensions Table'[WORKING_DAYS] ) )
RETURN
AmtPerDay

 

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,

Dedmon Dai

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors