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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
HenryKrinkle
Regular Visitor

Report Formatting Query - SSAS Live Connection

Hi Community,

 

I have been using Power BI for a short amount of time and had a few reports setup and shared, using an SSAS live connection.  The setup was simple and the product is a joy to use.

 

I was recently asked to make all Power BI tables consitent with the output from another reporting tool we use and dont really have all the answers as to whether or not this is possible.  Looking around the community I can sort of piece together a few limitations with the way I am using the tools/service.

 

 - I have a table with monetary amount to 2 dp's, TY v LY.  I want to rename the columns to make better use of screen real-estate, change the formatting to thousands, and put the negatives in brackets, coloured red.  My understanding is that for the naming and formatting, I would need to create new meausres in the underlying model, this is more admin in the model but thats fine as the new measures are just pointers - but what can I do about the colouring in this situation?

 

 - If I use the SSAS 'import' feature, does this still take into account the Row Level Security we have built in or would I have to-re-specify this in the model that is now imported into desktop/service (I have PBI Pro Licenses)? 

 

What are the options here?

 

Kind regards and thanks in advance

 

Henry

1 ACCEPTED SOLUTION
v-qiuyu-msft
Community Support
Community Support

Hi @HenryKrinkle,

 

 - I have a table with monetary amount to 2 dp's, TY v LY.  I want to rename the columns to make better use of screen real-estate, change the formatting to thousands, and put the negatives in brackets, coloured red.  My understanding is that for the naming and formatting, I would need to create new meausres in the underlying model, this is more admin in the model but thats fine as the new measures are just pointers - but what can I do about the colouring in this situation?

 

Which SSAS model did you connect to, cube or tabular?  From your description, it seems that you are connecting to tabular model. Based on my test, when we connect to SSAS tabular or cube in live connection, we are not able to modeling data in desktop additionally, as all format options are greyed out and disabled. To color the values, after put values in a visual, there should be a property to format color. Like in table visual:

 

q4.PNG

 

- If I use the SSAS 'import' feature, does this still take into account the Row Level Security we have built in or would I have to-re-specify this in the model that is now imported into desktop/service (I have PBI Pro Licenses)? 

 

On-premise row level security with Power BI is only available with Live Connection. When connecting to SSAS in live connection, RLS are defined on SSAS side. But if we use import mode, we need to define Row-level security in Power BI desktop and  set in service follow this article: Row-level security (RLS) with Power BI.

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-qiuyu-msft
Community Support
Community Support

Hi @HenryKrinkle,

 

 - I have a table with monetary amount to 2 dp's, TY v LY.  I want to rename the columns to make better use of screen real-estate, change the formatting to thousands, and put the negatives in brackets, coloured red.  My understanding is that for the naming and formatting, I would need to create new meausres in the underlying model, this is more admin in the model but thats fine as the new measures are just pointers - but what can I do about the colouring in this situation?

 

Which SSAS model did you connect to, cube or tabular?  From your description, it seems that you are connecting to tabular model. Based on my test, when we connect to SSAS tabular or cube in live connection, we are not able to modeling data in desktop additionally, as all format options are greyed out and disabled. To color the values, after put values in a visual, there should be a property to format color. Like in table visual:

 

q4.PNG

 

- If I use the SSAS 'import' feature, does this still take into account the Row Level Security we have built in or would I have to-re-specify this in the model that is now imported into desktop/service (I have PBI Pro Licenses)? 

 

On-premise row level security with Power BI is only available with Live Connection. When connecting to SSAS in live connection, RLS are defined on SSAS side. But if we use import mode, we need to define Row-level security in Power BI desktop and  set in service follow this article: Row-level security (RLS) with Power BI.

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Qiuyun Yu,

 

Thank you for your reply, I appreciate you taking the time.

 

You are correct, I am live connecting to an SSAS tabular model.

 

The colouring would need to be conditional, so a value within a column is coloured red only if the value is negative.

 

From the looks of things, I think the path forward is, as long as I can maintain Row Level Security, is to drop the live connection, import from SSAS and refresh daily, which to be honest is the time frame I'm working on now.  It seems I would then have more report design features available. 

 

This scenario seems to be quite common and is one feature that, in my opinion, would change Power BI into a killer solution.

 

Kind regards and thanks again

 

Henry

Hi @HenryKrinkle,

 

To set red color for the negative values in a table visual, you can use Conditional Formatting.

 

z1.PNGa2.PNG

 

Reference:
Conditional formatting in tables

Tips and tricks for color formatting in Power BI

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.