Forum Discussion
Function 'FORMAT' is not supported in DirectQuery mode
There are two potential solutions:
1. If all you want is to have a thousands separator, you can set the formatting on your measure:
2. You can use the Format function in DQ mode if you go to Options -> DirectQuery and click "Allow unrestricted measures in DirectQuery mode". Note that this will enable other functions whose performance is poor in DirectQuery mode, so save often.
I'm not sure why the Format function would be disabled, though. I'll inquire.
There are two potential solutions:
1. If all you want is to have a thousands separator, you can set the formatting on your measure:
2. You can use the Format function in DQ mode if you go to Options -> DirectQuery and click "Allow unrestricted measures in DirectQuery mode". Note that this will enable other functions whose performance is poor in DirectQuery mode, so save often.
I'm not sure why the Format function would be disabled, though. I'll inquire.
Solution 2 is the one I went with:
Don't see this option in Power BI Desktop anymore. Is there a modern option?
Why I only have this option? Please advise. Thank you
Meant to include this screenshot in my previous post:
I inquired about why the FORMAT function is disallowed in DQ mode, and the reason is because, if it is used unwisely, it could cause a performance issue. Specifically, we cannot rely on the backend datasource to do the formatting. So, if you were to use it within a table scanning function, that would force the calculation engine to request all the rows of data from the datasource so that the formatting could be performed on each row locally. This would be the case if you had a measure like this:
=AVERAGEX(BigTable, LEN(FORMAT([Value],"#,###")))
This example is utterly contrived, and I can't think of a non-contrived example. I think it is vastly more common to use FORMAT on the final result, which is perfectly safe. So, you can safely use "Allow unrestricted measures in DirectQuery mode" for your scenario.
JeffDuzak, more than a performance concern, I think the problem with using FORMAT() rather than the measure metadata is that a measure using FORMAT() will run into issues as input to another numeric function. For example, if I have two FORMAT()ed measures, and I want to add their values together, I'd run into problems with calling addition on strings.
