Dax Calculation - related connection

Hello Team,

I am having trouble to do a basic DAX calculation with sumx & related.
I have local currency value and product in one table & product & fx rate in another one.
When I type the Sumx and related the related doesnt let me input a column name (here the FX column from my other table). Many thanks for the guidance,
Cheers,
Julie

Hi Julie,

In order to use the RELATED function you need to ensure that there is relationship between the two tables. In your case this would be between your two product columns.

If you create a relationship then you should be able to use the function.

If this isn’t the case please post a sample PBIX file so can diagnose further.

Regards,
H

1 Like

Hi @Julieee299,

Have a look at these and see if it helps you…

https://info.enterprisedna.co/dax-function-guide/userelationship/

https://info.enterprisedna.co/dax-function-guide/related/

2 Likes

Thanks a lot :slight_smile: Will check this and in case I still have issue will come back.
Many thanks for the reactivity,
Julie

Thanks a lot Haroon - I have relationship but its not working maybe I do something wrong. Will look at the link your colleague shared below to try to find a solution but if I am still having troubles will come back with the Pbix file atttached.
Many thanks for the reactivity,
KR
Julie

Thanks for posting your question @Julieee299. To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.

Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, a screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Including all of the above will likely enable a quick solution to your question.

Hello Haroon,

1)I am reaching back cause even after looking at the Supply Chain webcast yesterday evening I still didnt find the error in the calculations I am trying to achieve.
The value in the current raw data are in Local currency and I would like to translate them to GBP thanks to the mapping table attached. There is a common column in both file (relationship activated) which is the name of the factory.

2)Also If you have any idea on how to calculate the YTD amount based on the current month (current month formula is ok) but then I need to pick the right current month of the column and add all the previous one to reach to YTD good number (e.g if current is Feb here I would like to sum the Jan and Feb LC translated in GBP)
I tried to unpivot the month column but dont think is the good approach here.

I have attached pbix file :slight_smile: (did the best practice that Sam shared yesterday for inactivating the dates relationship ) & then userelationship function & I downloaded the new dates table provided during the webinar.

Many thanks in advance,
Take care & Stay safe,
Kr
Julie

DNA test.pbix (874.2 KB)

Hi Julie. There are often many ways of doing the same thing in Power BI, and here’s one possible solution for you. I’m not exactly sure of what your desired end state is here, but hopefully the DAX can be of help. GregTEST Related Connection.pbix (844.7 KB)

1 Like

Hello Greg
Thanks for your prompt support.
I have checked and its working indeed but could you explain me for the YTD value (currently base on the what if function that is selecting the “selected month value”.

If I manager to have my current month with the set up I need (like always will be 1 month before the current one) then I can delete the step 2 of your note "for testing, added [Selected Month] “What-if” parameter (which automatically created [Selected Month Value] measure) ".

And then modify the YTD GBP formula with the current month instead of the selected Month value correct ?

Would you work on the query editor to unpivot all the months to make the calculation easier after ?
I will need to develop some calculations like difference between plan date and LV data / or rank the project etc etc but what would you recommend here with a model looking like this one.

Many thanks again,
KR
Julie

Hi Julie.

The SWITCH TRUE() statement in the [YTD GBP] measure is easier to code/read than multiple nested IF statements; I had the same thought about modifying the [YTD GBP] measure to use your [Current Month] measure.

As for the data model, I agree that unpivoting the month columns in a duplicate of the [Raw Data] table would make it much easier, especially if your reporting crosses year boundaries (sample attached). I don’t have recommendations for your current model.

Good luck.
GregTEST Related Connection 2.pbix (121.9 KB)

1 Like

Hello Greg,

Just checking and indeed very good i do think the unpivoting will be useful going forward for deep dive other measures.
About the FX, in the table its the average GBP associated to each local currency so indeed I ll need to divide to get the GBP amount.
I will rework the model again to see some other formulas - will come back to you if needed but thanks very much you were a very very great support.
Kind regards & Happy Easter.
Take care
Julie

Hi @Julieee299, Welcome to the Forum! It’s great to know that you are having a great experience so far. We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

Hello Greg I hope you are doing well. I have continued working from the model as with your guidance which was super helpful. I just post something on the forum but I didnt get any reply so far I wonder if maybe you could help here again ? Many thanks in advance. Kind regards,
Julie