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.
Hi,
I have a table Booking imported from an Excel file
ID | Code | Memberschip | Installment | Commission |
1 | B | 86,94 | 60,00 | 250,00 |
2 | C | 19,44 | 255,00 | 750,00 |
3 | B | 7,86 | 705,00 | 2000,00 |
4 | C | 33,00 | 75,00 | 240,00 |
5 | C | 60,04 | 120,00 | 390,00 |
6 | C | 78,44 | 420,00 | 1300,00 |
7 | A | 7,46 | 615,00 | 1700,00 |
8 | A | 67,16 | 105,00 | 350,00 |
9 | C | 38,10 | 30,00 | 120,00 |
10 | C | 97,44 | 120,00 | 420,00 |
11 | C | 65,04 | 180,00 | 550,00 |
12 | A | 2,95 | 105,00 | 286,00 |
13 | C | 65,59 | 255,00 | 760,00 |
I want to add a new column Report_Commission using a table Commission_Parameters I created by using "Enter data":
Code | Tax% | Commision% |
A | 0,0925 | 0,25 |
The two tables are linked:
To add the new column, I use "New Column" in the Home tab
Report_Commission = IF(Booking[Code]="A";Booking[Memberschip]+Booking[Installment]*0,25/0,0925;Booking[Commission])
Question 1: Why can't I find the table Commission_Parameters in the autocompletion?
Question 2:
When I explicitly add the two fields from Commission_Parameters:
Report_Commission = IF(Booking[Code]="A"; Booking[Memberschip] + Booking[Installment] * Commission_Parameters[Commission%] / Commission_Parameters[Tax%]; Booking[Commission])
why do I get the error: "A single value for column 'Commission%' in table 'Commission_Parameters' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."?
Question 3: Why does this error persist even when I add an aggregation to the fields Commission_Parameters[Commission%] and Commission_Parameters[Tax%]?
Thanks,
R.W.
Solved! Go to Solution.
The answers to almost all of your questions are to do with how you link to the other table from the 'many' side.
You have to use the RELATED function. The intellisense (autocompletion) is smart enough to give you only items that it thinks you can use according to the template of the functions you call.
hi,
you could also join the information as additional columns using power query - I like to do this for these cases
https://docs.microsoft.com/en-us/power-bi/desktop-shape-and-combine-data
best regards,
florian
The answers to almost all of your questions are to do with how you link to the other table from the 'many' side.
You have to use the RELATED function. The intellisense (autocompletion) is smart enough to give you only items that it thinks you can use according to the template of the functions you call.
Hi HotChilli,
Thanks, this works
Report_Commission = ROUND(IF(Booking[Code]="A"; Booking[Memberschip] + Booking[Installment] * RELATED(Commission_Parameters[Commission%]) / RELATED(Commission_Parameters[Tax%]); Booking[Commission]);2)
R.W.
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 |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |