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
dyingod
Helper I
Helper I

Neither M or DaX formulas recognised

Hello!

 

I've just spent 2 hours searching for a solution in here & google. I failz. Please help! 🙂

 

I have 2 unrelated tables that shouldn't be related anyway.

 

One of them is simply querying a public API for latest exchange rates and only has a single row.

 

I'll represent it as RUB2GBP[XR]

(to be honest... this was like a list or record... and I'd love to keep it like a list or record... I say this or that coz because I don't recognise the icon, here: icon.PNG. If I can query whatever this is without transforming it into a table, that'd be AMAZING)

 

The other is a table in which I need to create a custom column to use that exchange rate from the other table.

 

VK is the other table. I'd like to create a custom column called [Cost] where:

 

= VK[Cost in Russian Rubles] * RUB2GBP[XR]

 

i don't know if this is because they're not related but whether I use:

 * MAX(RUB2GBP[XR])

* SUM(RUB2GBP[XR])

 

Nothing works. It just says "Expression.Error: The Name "X" wasn't recognised.

 

Can someone help?

 Table 1 - which queries public API for a single exchange rate (that I need to use)Table 1 - which queries public API for a single exchange rate (that I need to use)Table 2 - I need to create a custom column converting SPENT (in Russian Rubles) to GBPTable 2 - I need to create a custom column converting SPENT (in Russian Rubles) to GBPThis is one of my gazillion attempts to use either M or DaX and always ends with the "wasn't recognised)This is one of my gazillion attempts to use either M or DaX and always ends with the "wasn't recognised)

PS.: this is not per se Power BI Power Query but I will also use this in there, the problem exists in Excel Power Query.

 

Thank you

 

Raf

1 ACCEPTED SOLUTION

@dyingod,

 

As mentioned above, you could convert the record into a table and then add to data model. That way, you may use DAX to add a calculated column.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
Greg_Deckler
Super User
Super User

It looks to me that you are trying to use DAX expressions where you should be using Power Query "M" Code. You could import the data into your data model and then create the custom columns/measures using the DAX that you have. But you need to do that in your data model in Excel and not in Power Query editor. See this article:

 

https://community.powerbi.com/t5/Community-Blog/The-Languages-of-Power-BI/ba-p/69104

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hello! Thanks for this.

 

I tried both M and DaX, same issue.

 

Is there another way I could import the exchange rate API, which is a tiny URL, directly into the custom column formula? Rather than creating it as a separate table/list/query in itself?

 

Here are some screenshots if this helps?latest attempt using Mlatest attempt using MVK is the table I want to import exchange rate toVK is the table I want to import exchange rate toRUB2GBP is the query with exchange rateRUB2GBP is the query with exchange rate

@dyingod,

 

You may combine the two queries in Advanced Editor, and add custom column as follows.

Record.Field(Source2, "RUB")
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks v-chuncz-msft!

 

Is that the only way? seems a shame to screw up a nicely modelled table with a weird single-value column but yeah if that is what it takes then yes pls! 🙂

@dyingod,

 

As mentioned above, you could convert the record into a table and then add to data model. That way, you may use DAX to add a calculated column.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.