Query Folding error in Direct Query

[Fern_21]

Hi all!

I've a report connected to tables in DirectQuery. In a filter, I need to insert a concatenation between two fields, and I try to create it either in Power Query or directly in DAX with this expression:
Facility = CONCATENATE(facilities[facility_code], " - " & facilities[facility_name]).

However, when I apply the filter, the visual objects on the page return this error. 

If I use the facility_code field instead, it works. How can I fix this?

Thanks

[v-kathullac]

Hi @Fern_21 ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithanya.

[v-kathullac]

Hi @Fern_21 ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithanya.

[v-kathullac]

Hi @Fern_21 ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithanya.

[lbendlin]

Is the facility_code not sufficient as a filter?  What would you need the name for?

[grazitti_sapna · Accepted solution]

Hi @Fern_21,

The problem here isn’t the syntax of your DAX expression, but the limitations of calculated columns and model transformations in DirectQuery mode.

When using DirectQuery, calculated columnsare not pushed down to the data source — they’re evaluated in the Power BI engine after the data comes in. This breaks filtering because filters need to be translated into SQL and sent to the source, and your concatenated column doesn’t exist in the actual SQL schema.

1. Try creating the concatenated column in Source database (SQL).

2. Create Calculated column in Import mode as direct query will not support it

3. Use both fields in separate slicers/filters

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[Fern_21]

Hi @grazitti_sapna !

Is it better to avoid using calculated columns in DirectQuery mode?

I also get an error for the unpivot operation.

Calculated columns and unpivot are not directly supported in DirectQuery?

Thanks

[lbendlin]

You should avoid any transforms that break query folding.