Report Formatting Query - SSAS Live Connection

[HenryKrinkle]

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

[v-qiuyu-msft · Accepted solution]

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:

- 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

[HenryKrinkle]

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

[v-qiuyu-msft]

Hi @HenryKrinkle,

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

Reference:
Conditional formatting in tables

Tips and tricks for color formatting in Power BI

Best Regards,
Qiuyun Yu