cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
v-dabatc
Microsoft
Microsoft

Function 'FORMAT' is not supported in DirectQuery mode

I'd really like to format the aggregated number I get from my data source.  It's a large number that could use a thousands separator to make it more human readable. 

 

But after discovering the FORMAT function, and trying to use it in a report with DirectQuery, I'm hit with the following error message:

 

Function 'FORMAT' is not supported in DirectQuery mode

 

As an unacceptable workaround I found, which is to Import the data instead of using DirectQuery (my data source is too large to be importing the data), here is what I'm after:

 

ThousandsSeparator.PNG

Here's the formula:

 

TotalCustomersFormatted = FORMAT(SUM(AggregatedTotalCustomers[TotalCustomers]), "#,###")

 

We could really use the FORMAT function in DirectQuery mode.

1 ACCEPTED SOLUTION
JeffDuzak
Microsoft
Microsoft

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.

 

 

View solution in original post

9 REPLIES 9
JeffDuzak
Microsoft
Microsoft

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:

 

FormatInDirectQuery.PNG

Anonymous
Not applicable

image.png

 

Why I only have this option? Please advise. Thank you

Don't see this option in Power BI Desktop anymore. Is there a modern option?

Meant to include this screenshot in my previous post:

 

Formatting.png

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.

greggyb
Resident Rockstar
Resident Rockstar

@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.

I agree, that's a problem with using the FORMAT function, and I agree, it's better to set a format on the measure instead of using the FORMAT function.

 

The performance concern only related to the question of why the FORMAT function is disabled by default when you're in DirectQuery mode.

Greg_Deckler
Super User
Super User

Can you use the Paintbrush icon to change the format? Click visual, click paintbrush icon, data label, set Display to None and Decimal to 0?


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors