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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
nsam101
Regular Visitor

Number format from SSAS cube lost

I have a number of measures defined in my multidimensional AS cube where the number of decimal places is explicity set, either directly against the incoming database measure or in a cube calculated measure (e.g. FORMAT_STRING = "#,#"). These work fine everywhere else, in reports and Excel, etc. However Power BI ignores them and displays the measures to many decimal places. 

 

I have tried the solution suggested here to no avail: https://community.powerbi.com/t5/Integrations-with-Files-and/Number-format-from-SSAS-cube-lost/m-p/1...

 

Any other suggestions? It's a real barrier to user adoption of Power BI if 15 digits are displayed where 2 would suffice, especially when viewed on a mobile device.

 

Funnily enough, the behaviour varies depending on whether the report is viewed in Power BI Desktop (4 decimal places) or Power BI Online (15 decimal places).

6 REPLIES 6
xppdev
Frequent Visitor

Same issue here, and I belive we used to be able to categorize data as well. I vaguely remember assigning City and other address info in our proof of concept. The formatting really needs to be in the Power BI tool as we are trying to get our users to utilize more of a self service reporting model. Ex, some users may not want to see any decimals. Or even just show the thousands.

We can not import cubes or tabular models either. Once I add a date dimension, I get an immediate memory error. We are working through MS to try and resolve, but no answers as of yet. These issues are making it difficult to implement the product.

I'm facing a possibly related problem where I apply some case transformations (uppercase, lowercase, etc.) to string columns in the SSAS model using the Query Editor, but those changes are ignored in Power BI.

mchapman
Advocate I
Advocate I

Same issue here.  Looks like you can make it work if you set the format_string when you create a calcualted member like this:

CREATE MEMBER CURRENTCUBE.[Measures].[Gross Margin Percent]

AS DIVIDE([Measures].[Gross Margin],[Measures].[Net Sales]),

FORMAT_STRING = "Percent",

NON_EMPTY_BEHAVIOR = { [Net Sales] },

VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Measures' ;

 

But if you calculate that member outside of the create statement the formatting doesn't hold.  I haven't been able to get any formatting to pass through on any stored members no matter where I set the format.

 

This is frustrating as I spent the entire year trying to sell this tool to the business and now I have people buying in and this is a project derailer for most of them.  Seems like this should be a much bigger issue.  Are there just not that many people using the live connections yet?

 

I'm only interested in live connections against cubes as we have team members all over the world and security is already set up in every cube. 

ankitpatira
Community Champion
Community Champion

@nsam101 Are you connecting live or importing data. Becuase if you're importing then you can control data format and type via query editor and under Modelling tab. If connecting live I would suggest you change format under cube and see if it makes any difference. I have seen this before and I would also suggest you submit it as a bug here.

I'm connecting live. I have tried changing the formatting in the cube, and although it's reflected in Excel pivot tables it's ignored by Power BI. So it does seem like a bug to me.

Anonymous
Not applicable

We have same issue here (measures in % in a cube, but displayed as decimal in PowerBI). I was just starting my research, but it seems it's a real issue.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.