topic Re: DAX issue on published dataset in Tabular Editor but works on direct query version in DAX Commands and Tips

DAX issue on published dataset in Tabular Editor but works on direct query version

mhamilt1 — Sat, 16 Dec 2023 13:21:39 GMT

We have a published model that I need to add a few tables to. I dont have the original pbix file.

I added 2 tables for Customer Segments and added a formula column. I get an error about the DAX formula but I can get this to work on a different new offline direct query power bi model.

I added joins and put this formula in:

SUMX ( 'Customer Segment Bridge', 'Customer Segment Bridge'[segment_allocation] * RELATED ( 'Flight Fact'[#Pax Quantity] ) )

Error

Expression: The column 'Flight Fact[#Pax Quantity]' either doesn't exist or doesn't have a relationship to any table available in the current context.

but it does. I can share the .bim file.

Any help how to debug will be great!

Re: DAX issue on published dataset in Tabular Editor but works on direct query version

some_bih — Sat, 16 Dec 2023 19:26:53 GMT

Hi mhamilt1 to use RELATED you need to have regular many to one relationship in model.

Check your model for this part.

Useful link below

https://dax.guide/related/

Re: DAX issue on published dataset in Tabular Editor but works on direct query version

mhamilt1 — Sun, 17 Dec 2023 10:30:56 GMT

Hi some_bih, I do have a many to 1 relationship. Is there any log or way to share the file to get some support that way. As far as I can see, I make the same join and formula in a new pbi workbook, publish it and it works but the one that is already published that I extend using tabular editor does not accept the formula. The tables are related because I can select from them and the joins work.

It is a funny join though. It is a many to one from a dimension bridge table to a fact table. The fact table may have joins to other dimensions which are many to one from fact to dim but the formula does not work if I only use the 2 tables.

Re: DAX issue on published dataset in Tabular Editor but works on direct query version

some_bih — Sun, 17 Dec 2023 17:46:29 GMT

Hi mhamilt1 some of functions are not supported in Direct query. Sharing files could be via Office 365 or some others providers like Dropbox, Google drive. Hide sensitive data and sharw link

Re: DAX issue on published dataset in Tabular Editor but works on direct query version

mhamilt1 — Fri, 22 Dec 2023 09:03:44 GMT

We solved this by removing the RELATED() function and changing the #Pax_Quantity to reference a calculated measure column. Im not sure why the original formula did not work on a published model but did on a direct query model. However, It is solved so we can close.

SUMX ( 'Customer Segment Bridge', 'Customer Segment Bridge'[segment_allocation] * [#Pax] ) )