Forum Discussion

v-dabatc's avatar
v-dabatc
Microsoft Employee
10 years ago
Solved

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 FOR...
  • JeffDuzak's avatar
    JeffDuzak
    10 years ago

    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.

     

     

greggyb's avatar
greggyb
Resident Rockstar
10 years ago

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.

JeffDuzak's avatar
JeffDuzak
Microsoft Employee
10 years ago

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.