Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MHEnsslen
Frequent Visitor

Issues with Format Expressions in Calculation Groups

Something seems to have changed in service between Friday 1/22 and 1/25.   I hadn't republished  so I hadn't made changes.

 

I have measures that I want to be able to flip between units and $s.  So in my measure's format I had a format string of "FLEX".

I then have a Calculation group that is used exclusively for reformatting these measures. The Format string expression is:

VAR MeasureFormat = SELECTEDMEASUREFORMATSTRING()
VAR SkipConversion = MeasureFormat <> "FLEX"
RETURN
IF ( SkipConversion,
MeasureFormat,
"#,0"
)

 

However, this morning in service the report that utilizes these measures were all returning "FLyX"

Unsure of what the E might be replaced with, I thought I'd try something else and instead replaced the FLEX in my expression to °°° figuring the degree symbol can't really be translated, if it were a regional setting or something (which I also confirmed are all correct).

 

With that change to the °°° I can now see the acutal number values in service, but the formatting I have entered in the expression is still not carrying across to PBI service.    No Commas in my unit measures and no $ or consideration for the decimals in my dollar format expressions.  

 

Again my model looks perfectly fine in PowerBI desktop on multiple computers.  So I am at a loss to what could be the issue.

 

2 REPLIES 2
v-yuaj-msft
Community Support
Community Support

Hi @MHEnsslen ,

 

Can you share some screenshots around the issue please? This is not helping much.

Can you share some sample data and the expected result to have a clear understanding of your question?

You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.

 

Best Regards,

Yuna

In the image below (From PowerBI Desktop) you can see 3 measures in my matrix. 

NIS Units and NIS $ are regular explicit measures summing up the columns in my fact table.

NIS is a dynamic measure that is supposed to allow the user to use a slicers from the Calculation Group "Unit of Measure" to be able to dynamically flip between 6 different calculation Items (List $, Sales $, Std Cost $, Std Margin $, Std Margin%, Units)  

 

In Desktop Working Correctly.png

 

As you can see NIS $ and NIS Units have the same value no matter what Unit of Measure is selected (Expected behavior).

NIS is switching between the measures and giving me the desired results, including the formatting specified by the Format Expression on each of the Calculation Items.  

 

The Measure "NIS" has a Format string of "FLEX" which the Format Expression is looking for to either dynamically format the result, or return the measure's set format (as it does with NIS $ and NIS Units above)

 

The Format Expression of the Calculation item for Units


VAR MeasureFormat = SELECTEDMEASUREFORMATSTRING()
VAR SkipConversion = MeasureFormat <> "FLEX"
RETURN
IF ( SkipConversion,
MeasureFormat,
"#,0"
)

 

The Expression of the Calculation item for Units

VAR Flexmeasure = SELECTEDMEASURENAME()
RETURN
SWITCH(Flexmeasure,
"NIS", [NIS Units],
"Open", [Open Units],
"Budget", [Budget Units],
blank()
)

 

However, when I publish this pbix to service, I get the following result.

Service Working Incorrectly.png

 

NIS $ and NIS Units still calculate and format as expected, but the NIS measure does not, instead it returns "FLyX". 

 

I have tried exchaning out FLEX with other strings, but to no avail.  Desktop still works as expected with these other strings but service continues to return incorrect results.   

Other stings will return the correct value, however with the incorrect formatting.  

For example, units will show 425621405.52000001

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors