Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello
Imagine I have two tables:
Table 1:
ID CountryID Money
1 MEX 10
2 USA 20
3 USA 30
Table2:
ID CountryID Currency
1 MEX 2
2 USA 3
So the idea, or what I need, is to travel all along the table1, and for each one row, check the countryID search in Table2 for that countryID, pick the value of currency, and multiply to the money column of Table1
In a programming language, I think that could be:
for(rowTable1 in table1)
{
for(rowTable2 in table2)
{
if(rowTable1.countryID == rowTable2.countryID)
{
val = rowTable1.money * rowTable2.currency
rowTable1.money = val
break
}
}
}
But I'm not sure how to implement this in an "easy way" in Power BI, when I say in an easy way, it's that maybe it's not neccesary to do a lot of stuff and exists an easy option I didn't think about.
Thank you and regards
Solved! Go to Solution.
as I see your Data Model - the easiest way is to create relationships 'Table1'[CountryID] -> 'Table'[CountryID] (Many to one)
then create a calculated column in Table1:
Column = [money]*related('Table2'[Currency])
do not hesitate to give a kudo to useful posts and mark solutions as solution
Linkedin
as I see your Data Model - the easiest way is to create relationships 'Table1'[CountryID] -> 'Table'[CountryID] (Many to one)
then create a calculated column in Table1:
Column = [money]*related('Table2'[Currency])
do not hesitate to give a kudo to useful posts and mark solutions as solution
Linkedin
Hi @DavidMoreno504 ,
First, Create relationship between ID column of both the table. Then Create one new mesure like below:
Measure = Sum(Money)*Sum(Currency)
Then you'll get your expected result.
Step 1: Create relationship between both the tables.
Step 2: Create calculated meausre like below:
Please give KUDOS to this effort and accept this as a solution.
Let me know if my recent post helped you or not.
Of course your anwser a really helpful, but it doesn't give what I want as I tell in the post before
I reallly appreciate your help, thanks
That's not working as I would like
The problem of that is that first sum all the values so you get, 10 + 20 + 30 = 60
Then, multiplies 60 * 2 = 120 and 60 * 3 = 180
Finally does 120 + 180 = 300.
But what I want is,
For Mexico, 10 * 2 = 20
For USA, we have 2 values:
20 * 3 = 60
30 * 3 = 90
So the total would be: 20 + 60 + 90 = 170
Thank you for your time
really?
just set do not summarize in Money-field properties
do not hesitate to give a kudo to useful posts and mark solutions as solution
Linkedin
Ok, now it works, I used your formula instead of the other one, and works fine.
Thank you both!
Hi @az38
Don't know why is not catching it. Attach some pics
So this is the relationship
The details of that relationship, related by CountryID and many to one, both directions
The measure created and it's formula (the same that you posted)
And don't summarize (no resumir)
The result I get
Something it's wrong but I don't know what.
Thank you and regards
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |