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!

BUG : Matrix visualisation is not respecting formatting from SSAS cube in direct connect

The formatting defined for an  MDX Cube calculated measure from an SSAS cube is not being followed when displayed in the Matrix visualization in Power BI. Two decimal points are being displayed whereas formatting defined in the cube  indicates NO decimal places.

 

This is using SSAS Direct connect mode. 

My SSAS cube has been built with Visual Studio 2015 Version 14.0.25420.01 Update 3.  It is running on SQL server analysis services version 3.0.4206.0.

 

Please test this behaviour against an SSAS cube in direct connect mode. You will have to create a calculated measure in the Calculations tab of  the Cube designer and make it a similar type of measure. Meaning a measure sliced by a dimension member.

 

 

1. Firstly below is the cube calculation and formatting definition 

Leads Open Cube MDX Calculation.PNG

2. Next Excel showing the measure correctly with No decimals 

 

Leads Open Excel.PNG

 

3. Lastly the Matrix visualisation showing the measure with 2 decimals places. Note there is no optional available to format this display to NO decimal places.

 

Leads Open Power BI Matrix.PNG

Status: Needs Info
Comments
v-jiascu-msft
Employee

Hi @inayatkhan,

 

Power BI works fine in my test. Are there any other details?

1. SSAS version is 13.0.4210.6 that is newer than yours;

2. Power BI Desktop version: 2.57.5068.721 64-bit (April 2018);

3. Before modification.

Matrix_visualisation_is_not_respecting_formatting_from_SSAS_cube_in_direct_connect1

 4. After modification.

Matrix_visualisation_is_not_respecting_formatting_from_SSAS_cube_in_direct_connect2

 

Best Regards,

Dale

Vicky_Song
Impactful Individual
Status changed to: Needs Info
 
inayatkhan
Frequent Visitor

Hi Dale,

 

1 ) No that is all I did. 

2)  The Images in points 3 and 4 of your post are only showing the broken image symbol. Please could you post them again as jpg, png. etc...

3 ) Please could you include the cube Mdx code snippet that you used to define your Measures 

4 ) If you used Adventure Works I could try and replicate it on that using your code. Thanks.

5) My assumption is the Power BI matrix visualisation takes its formatting instructions directly from the data sent back from the SSAS cube on the values displayed. Is this assumption correct ?

 

Thanks Inayat Khan

 

 

v-jiascu-msft
Employee

Hi Inayat,

 

Yes, I tested it with Adventure Works. 

3, 4. I just change the format string of a measure [Internet Gross Profit]. 

I upload the snapshot here. Please check them out.

 

Best Regards,

Dale

 

inayatkhan
Frequent Visitor

 

 Hi Dale, 

 

The issue is that Gross Profit measure is NOT a measure sliced by a dimension member. I have other calculated meaures similar to Gross Profit. For Example Variance USD this is respecting the formatting.

 

CREATE MEMBER CURRENTCUBE.[Measures].[Variance USD]
// AS NULL
AS [Measures].[Invoice Amount Budget USD] - [Measures].[Budget USD],
VISIBLE = 1 ,
DISPLAY_FOLDER = 'Revenue',
FORMAT_STRING = '#,#0.000';

 

This displaying the 3 decimal places as defined. Correctly.

Measure 3 Decimals in PowerBI.PNG

 

 So what you MUST do is create a Measure sliced by a Dimension Member and then you will see this issue occuring. Thanks

 

 

 

 

inayatkhan
Frequent Visitor

Hi Dale and Microsoft team,

 

Is there any further update on this issue ? I have you tested with the criteria I mentioned in my previous post posted ‎04-19-2018 02:22 AM ?

v-jiascu-msft
Employee

Hi @inayatkhan,

 

I tried it again. But I still can't reproduce it. Please refer to the snapshot below. They are all in the format I define in the SSAS cube.

Matrix_visualisation_is_not_respecting_formatting_from3

 

Best Regards,

Dale

inayatkhan
Frequent Visitor

Hi Dale,

 

I reviewed the measures and you DID NOT do what I asked which was to create / use  a measure that was "Sliced" and then check the formatting. 

 

To assist you I have create such a measure in that you can add to the Calculations script of the Adventure Works Cube.

 

Adjust the format string property and check if this is being used in the Power BI reports.

 

Create Member CurrentCube.[Measures].[Internet Sales Australia] as NULL,
VISIBLE=1,
Format_String = "#,#0",
Associated_Measure_Group = 'Internet Sales';

SCOPE ([Measures].[Internet Sales Australia]);

this= ([Measures].[Internet Sales Amount],[Customer].[Country].&[Australia]) ;

END SCOPE;

 

 

 

inayatkhan
Frequent Visitor

Hi , Any update on this post with the calcuation that I provided ? Thanks

inayatkhan
Frequent Visitor

Hi Moderators and Power BI staff, 

 

This post has had no response in nearly 2 months, please can you respond to my issue and resolve it. Thanks.