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
jhendrickson
Regular Visitor

Create a Calculated Column from Different Tables

I am attempting to add a column to a table in the Query Editor, using a column from a related table. I am getting an error "Token literal expected." The relationship between these tables has been set up. I've tried it with both the single quotation marks and without

Capture.PNG

1 ACCEPTED SOLUTION

Sorry that's my bad. I happen to work on a French system and it is using ";" rather than comma ",".

You need to replace all ";" by the proper separator which should be comma for you.

View solution in original post

10 REPLIES 10
parry2k
Super User
Super User

@jhendrickson you are using DAX formula in Query Editor which is not allowed. You can use same expression, and to do so, close query editor and add new coloumn in your table and then add this DAX expression.

 

Here is link for your reference on how to add columns.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Thank you for the information. I tried this but now I'm getting a different error - A single value for the column can't be determined. I may have a bigger issue. Here is what I am trying to do:

I have one table listing school districts and building codes (multiple buildings per district) and another table with the pupils per district per year:

Table 1                                                  Table 2

School    Building Code                        School   Year   Pupils

AB          1000                                        AB          2018  200

AB          1001                                        AB          2017  300

CD          1002                                        CD         2018  400

CD          1003                                        CD         2017  500

etc                                                          etc

 

Finally I have a large table with all the raw financial information for all the districts, every transaction with a column for the year and the building code. I can do great charts of the financial information by school but I want to include a calculation of expenses per pupil. I want to be able to drill down to look at things like instructional expenses per pupil or transportation expenses per pupil in any given year. It seems like the only way to do this is to get a column in the financial information table which divides each transaction by the number of pupils per school, and the only way to get that is through the building code and year. Is there a formula which would look at the year and building code column in each row, then pull in the appropriate number of pupils?

Hi @jhendrickson,

 

Could you post your real table structures(including the relationships) with just some sample/mock data, and the expected result? So that we can further assist on this issue? It's better that you can just share a sample pbix file. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. Smiley Happy

 

Regards

https://www.dropbox.com/l/scl/AAC88asXaxFoogfp3MGpSW2R6A-TPrTFxAU

 

This is a very truncated version of my data - thank you everyone for your help!

Hi @jhendrickson

 

I was able to recover the number of pupil in a new column in your "Sheet1" table if that is what you were looking after.

However, the dataset seems very complicated for me, especially the management of the dates.
PowerBI works magic with dates so it would be better to have full dates using PowerBI Date functions rather that several columns with period, month names etc. just a thought.

 

I created this column with the below DAX function:

Pupil number = 
var y=RELATED('Year'[Year])
var sc=RELATED(Schools[School])
var pupilnumber=CALCULATE(max('Pupil transposed (2)'[pupils]);'Pupil transposed (2)'[school]=sc;'Pupil transposed (2)'[year ]=y)
return pupilnumber

 

Is the below what you are looking for?

 School.JPG

Yes, that is what I want, but when I attempted this in my actual data set, I get this error:

 

Capture.PNG

 

 

Sorry that's my bad. I happen to work on a French system and it is using ";" rather than comma ",".

You need to replace all ";" by the proper separator which should be comma for you.

Hallelujah! Thank you!

Hi @jhendrickson,

 

Share the link from where i can download your file and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

share sample data in excel file and can give quick solution. 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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.