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
stevel
New Member

Formatting SSAS values (Live connection/explore live)

I'm running into an issue where I'm not seeing anything that will allow me to format any values I'm pulling from a live connection to a SSAS multidimensional cube.

 

Values Example

 

I would like to format the date in any format in general (01/02/2015, Jan 02, 2015, etc...) and "Cost" would format as $31,519.43 or even $31,519.

 

Is this possible to do with a live connection to a SSAS data cube?

1 ACCEPTED SOLUTION
greggyb
Resident Rockstar
Resident Rockstar

SSAS is a semantic layer in BI. This means it stores metadata natively with data. Part of this metadata is display formatting. Power View (the report component in Power BI) is designed, for better or worse, to defer to the display format metadata of its source. You should alter these display formats in your SSAS model.

View solution in original post

24 REPLIES 24
czheng
Frequent Visitor

 Any solution yet? I am not able to format the value from SSAS cube (live connection).......I just like to change decimal places from 2 to 1 on the percentage value.

 

czheng_0-1601344615728.png

 

Kab86
New Member

I am facing a similar issue:
with a live cube connection, i am having issues with thousand separator in random measures.


For example, for one specific measure, card visual shows numbers with the separator and line chart doesn't. (I have applied thousand separator format in the cube )

 

Any ideas please?

 

ashokojha
New Member

Not sure why its says - Solved!

I think this is a very big limitation of the tool. In real world a reprts formatting need will never drive to make changes to underline data source (cube). In fact its not possible to do. Two different reports can have different formatting needs which one you will put in Cube. Not good, something to work on asap for Power BI team.

 

As we say "You don't get second chance to make first impression!" this one is not good 🙂

ashokojha
New Member

Not sure why we tagged as - Solved!

This is a big limitation of Power BI at this point. I don't think reporting formatting need should drive underline data source change. If the cube is a data source for bigger group, one report's formatting need will not make cube change.

Braggilicious
Frequent Visitor

Since the fix in February, unless the aggregation function in the cube is SUM/AVG/COUNT then no formatting is applied to the measure. The ByAccount, FirstNonEmpty, LastNonEmpty, FirstChild, LastChild, AverageOfChildren aggregations do not display the correct format.

Anonymous
Not applicable

@jmford: this is weird because I have never changed the formatting of my calculations ("$#,##0;($#,##0)"). I entered it directly via the interface in VS early in 2016, and then it stopped working since December.

 

@Braggilicious: my calculation is based on a SUM, but formatting is not working though.

 

issue formatting.jpg

Anonymous
Not applicable

Hello @stevel, would you mind to change the status of the topic?

 

@jmford and I are having the same issue: SSAS MD formatting was perfectly working until the December 2016 update and since then it's no longer working.

 

I opened a MS ticket and Power BI Engineers basically said they're aware of the issue but have no ETA to provide us with, and no workaround for now.

 

This is really annoying, because we've been formatting fields for over a year in all our reports and suddenly it's broken with no alternative.

 

Displaying 3.00 instead of 300% is really confusing for the end-user, it's really basic but still a fundamental piece when it comes to user adoption.

In my case I was able to resolve this by backing out a load of changes to calculated members in my SSAS MD cube.  I am unclear specifically what the error (or char) was in the formatting that allowed the cube to deploy and calculate but corrupted the formatting in PowerBI.  However when I reverted the changes - the problem was resolved.  It was not a PowerBI service issue.

Braggilicious
Frequent Visitor

I have this issue, all my measures in the cube are defined as CURRENCY.   Excel and other reporting tools format the measures correctly, whereas PowerBI Desktop selectively formats the measures as either currency or decimal.  

 

Is there any way to enforce the formating on a on-prem cube?

Anonymous
Not applicable

Hello everyone,

 

Same problem here, I had my calculated measures formatted directly in SSAS with "+#,##0.00%;-#,##0.00%" and it's been working perfectly until November 2016. Since then, it's no longer working, it shows 0.00" in PBI.

 

Any help from a MS support would be greatly appreciate.


Thank you

I've had to raise a premier support ticket on this to get any response from Microsoft.  From the support engineer:

 

"After our local investigation, we found we can actually reproduce similar symptom in our local house. Basically in the backend Power BI will fire DAX queries against Cube directly (we call it DAXMD). At this moment, DAXMD still does not support formatting, so currency does not delegate over. "

Anonymous
Not applicable

Hello Braggilicious,

 

Thanks for your message. Unfortunately the support engineer's answer is partially incorrect when he says:

 

"At this moment, DAXMD still does not support formatting, so currency does not delegate over "

 

Maybe since the December 2016 update that is true, but before the formatting from a SSAS live connection was perfectly working.

 

Since that topic is considered as closed with an accepted answer, I created a new topic where you can see the difference before VS after the update here http://community.powerbi.com/t5/Desktop/Formatting-SSAS-values-with-a-Live-connection-WAS-working-un...

 

Hopefully your contact can have a closer look at the issue.

Before-Ater-Dec-16-update.JPG

 

Thank you

I have the same issue.  All SSAS MD formatting abruptly lost in PowerBI on 19th Jan.  Please fix ASAP.

greggyb
Resident Rockstar
Resident Rockstar

SSAS is a semantic layer in BI. This means it stores metadata natively with data. Part of this metadata is display formatting. Power View (the report component in Power BI) is designed, for better or worse, to defer to the display format metadata of its source. You should alter these display formats in your SSAS model.

Anonymous
Not applicable

This unfotunately is no longer working. I agree with the semantic layer concept but since November 2016, Power BI is no longer taking into account the SSAS formatting contains in the metadata. Please

Gotcha.  I was wondering if PowerBI could format the values like Tableau could with SSAS cubes.

Some visuals appear to have limited formatting.  Eg, if you click the format painter on a bar chart and expand the options for the X-Axis you will have the ability to control Display Units (Auto/None/Thousands/Millions etc) as well as the Decimal places.

 

But if you have a grid of data and want to have full control over each column that doesn't appear to be available yet.  You can always submit an idea (I will vote for you).

 

For now, the live connection to the MDX cube doesn't seem to always use the format_string values from the cube which is a shame.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Same problem here!

 

I tried to play around with SSAS format masks but they are not interpreted on Power BI Desktop when using live connections.

The only change I have seen is by converting to data to use Currency data type, the format is changes with thousands separators and 2 decimal places.

 

Has an issue been registered so far so that I can vote for it too?

I'm having this issue as well.
Formatted measures as \€ #,##0.00;\€ -#,##0.00 in the SSAS MD cube.

 

In Excel, everything is fine. In PBI Desktop, the € is missing.

Blog: sqlkover.com
Anonymous
Not applicable

Has there been any updates on this?   I good portion of the data I work with is using cubes in SSAS and the formatting is a big issue.

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.