When I create a simple calculation group in Tabular Editor, it greys out / disables the format dropdown in the formatting area of Measure Tools in Report and table view. I am still able to change formatting in the model view but this is not as convenient sometimes. I have tested this on two different existing models and a model created from scratch but the result is always the same.
I am curious if this is the normal behaviour and is the same for other users?
Having the same issue. Not tied to custom formatting in Calcualtion Group.
I tracked it down to one calculation. An image of the calulation item is below. There is no way to work around this. All calculations in the image cause the issue. Note: 1+1 and 1-1 cause the issue as well.
I think that it is due to the number of calculation items. My CG currently has 6. When I add one more, even one with the value of 1, it causes the issue.
Thats interesting, I played around a bit but couldnt replicate the behaviour using different amounts of calculation items and SAMEPERIODLASTYEAR. I did find that entering any numerical value in a calculation item instantly breaks the measure formatting. I asked wether this issue would be fixed in a twitter thread and some members of the power BI team replied that it would be fixed for External Tools GA, although I cant find any reference anywhere as to when this is planned.
Is this also the reason why none of the columns summarized anymore? All numeric columns are missing the sigmas and I can't even choose to sum those with down arrow in values section. All format options are availabe in ribbon but summarization just won't happened.
In a workaround I can use older version of the file, creates the visual with sums there and copying the visual into new file. The sums remains and everything works fine. And also, it is not very good practise to use columns straight so I normally use measures everywhere so the problem is not so huge. But in debugging purposes it is very handy to have the summarizing possibility and because of that, I really like if this will be fixed also.
I have also found that removing all custom format strings from calculation groups does not fix anything so once you go there, theres no going back. The only way I have found to fix the issue is to delete all calculation groups and start over. ✌
I've also been struggling with this since the External Tool integration was announced.
Here's how I can reproduce the issue:
1) Create a new PBI file in Desktop
2) Import data
3) Create a calculation group and 2 calculation items via Tabular Editor
4) Save the changes, and now measure formatting in Power BI Desktop is broken.
Here's a gif of what happens to the formatting features after these steps. Note that the format string changes when I use the format buttons, but I cannot set the format string directly nor can I choose "Whole Number" from the usual drop down. Also note that the visual does not refresh and update when I change the formatting; it only refreshes when I change the measure name.
@tenfingers, I can "fix" my PBIX by deleting the Calculation Group and then disabling Discourage Implicit Measures on the Model node in Tabular Editor.
If I only create 1 Calcuation Item that returns SelectedMeasure() then this problem doesn't seem to happen. When I add a second Item that returns AverageX, then it does. Deleting the 2nd item does not make this problem go away. I tried having every Item in the Calc Group return the same data type, whether double or String, the problem still happens.
I am not using Format String Expression on any Calculation Items.
I have found some further details around this.
It seems to be triggered by using a custom format string for a calculation item in Tabular Editor. It also creates a hidden folder containing every measure in the table (seperate folders for each table). The measures in the hidden folder contain the format string of that measure:
This doesnt seem like the correct behaviour to me.
It would also appear that I can no longer change the format string in Power BI at all. The option is available in the modelling tab but changing it appears to have no effect. The only way I can change the format of a measure is by using a custom format string in tabular editor.
Heard this exact thing mentioned on Guy in a Cube. Apparently it's a known issue and a fix is being worked on. Hopefully it's in the next release, it's making things painful for me creating and searching for measures. ✌️
I wonder if the measure uses FORMAT function to converts a value to text according to the specified format. All predefined formatting strings use the current user locale when formatting the result. The format strings supported as an argument to the DAX FORMAT function are based on the format strings used by Visual Basic (OLE Automation), not on the format strings used by the .NET Framework. Therefore, you might get unexpected results or an error if the argument doesn't match any defined format strings.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-alq-msft ,
Thanks for your reply, this actually affects all measures in my models. After creating a calculation group of any type, even with just a single basic SUM measure as a calculation item, the format option becomes disabled for all measures in the model. I have the measures in a measured table, I'm wondering if that has anything to do with it. In any case, I don't think any of the measures in the models use the DAX FORMAT function.
@tenfingers , You might be able to edit the measure created in Tabular Editor .
Refer if this option still works under model view. If the Model view in enabled
@amitchandak - Thanks, Yes I dont have an issue editing the format strings in Tabular Editor or in Model View in Desktop. Its just inconvenient not to be able to use the option in Measure Tools in Desktop. I want to make sure this isnt indicative of an underlying issue that will negatively impact my models by using calculation groups. If other users have the same experience then i will chalk it up as the expected behaviour and proceed with implementing calculation groups in my models.
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.