In this post I show you a very quick trick to format Line Chart and Area Chart conditionally in Power BI. As this is a “Quick Tip” I’m going to keep this post really short.
One of my customers asked me to show time series in line charts and area charts. But she want’s it to be conditionally formatted based on the average value over time. Let’s keep it simple, she wants to show “Sales by Year Month” in line chart, but, highlight the data points that are below “Average Sales per Year Month”. As you may know, we currently do not have the luxury of formatting line charts and area charts. But wait, this post is all about that. Let’s dig into it.
From the above scenario, you perhaps already guessed that we need to create a measure which defines the colour based on “Average Sales per Year Month” to be able to format the chart conditionally. If any data point is below the “Average Sales per Year Month” then we highlight it in Orange, if it is above the “Average Sales per Year Month” then we stick to the default colour.
When you work on real-world projects in power BI, you would probably have different environments Like DEV, UAT, Pre-Prod and Prod. It is important for you and your audience to know what the data is coming from. Am I looking at Dev or UAT data or I am actually looking at real data in Production environment. You may have asked or been asked with a question like “Where the data is coming from?”. It is important to know how trustworthy the data you’re analysing is. In this post I show you an easy way to show the environment your Power BI report is connected to.
How It Works
To display the environment name you use query parameters, then you reference that parameter, turn it to a table and add columns to show the environments accordingly. Easy right?
Depending on your scenario the implementation might be slightly different, but the principals are the same. In this post I use a SQL server database. Therefore I need to Parameterise server name. in real world you may also need to parameterise the database name. Again, if your case is quite different, like if you get data from Excel, then the Excel path can be different for different environments. Let’s dig-in.
Open Query Editor
Click “Manage Parameters”
Click “New”
Enter “Name” and “description”
Tick “Required”
Select “Text” in “Type”
Select “List of values” in “Suggested Values” and type in server names for different environments
pick a “Default Value” and “Current Value”
Click OK
So far you created a new parameter that can be used to get data from a SQL Server data source.The next steps show you how to use that parameter to show the environments in your reports.
There are a lot of discussions these days around Power BI tools to create reports and for sure many of you may have already downloaded and worked with some of them if not all of them. You may already think that some of the tools’ names are confusingly similar. I recently had an interesting conversation with a fellow who has a lot of SSRS report writing background. I was talking about Paginated reports and said, I downloaded the latest version of Power BI Report Builder… that he immediately said, wait for a second…
John: Power BI Report Builder? Oh I see, that’s the one that you can create paginated reports with then you can deploy those reports into an SSRS instance.
me: NOPE! That’s not the case I’m afraid.
John: Oh I know, I meant Power BI Report Server, you can deploy the reports to an instance of Power BI Report Server. I knew it!
me: NO! That’s not what I’m talking about…
John: What the…?
I bet some of you had a similar conversation with a friend or a customer. OK, in this post I explain a little bit about report authoring tools available to you and your organisation to get the most out of your Power BI ecosystem.
Here is a list of all reporting tools currently available to you:
Power BI Service: It is a SaaS (Software as a Service) offering from Microsoft in the cloud. The users in an organisation, based on their access rights, may be able to create and publish data, reports, dashboards in Power BI Service. The users can also schedule data refreshes on the published data as well as securely sharing and distributing the contents. While creating or editing reports is possible in Power BI Service, it is strongly recommended to avoid this method for several reasons. The most obvious one is that the changes you make in a report may be soon get overwritten by someone else that republishes the same report from Power BI Desktop. Check this blog post from SQLChick to see why you should avoid creating or editing reports directly from Power BI Service. The reports are downloadable in PBIX format. Use Power BI Service here.
Power BI Desktop: It is a desktop report authoring tool that can be used to connect to, or loading data from, varies types of data sources, preparing, transforming and cleansing that data and at last visualising the data. Power BI Desktop is the predominant report authoring tool with a lot more functionalities and flexibility than Power BI Service. For instance, setting up Role Level Security (RLS) is NOT available in Power BI Service. The format of the report file is PBIX. Download Power BI Desktop from here.
Power BI Report Builder (Paginated): Paginated reports aka “pixel perfect reports”, as the name resembles, are formatted in a way to fit perfectly on a page. That report page might later be printed. You have exact control over the page formatting to display your data in tables or charts. The reports are not as interactive as Power BI Desktop reports are. Paginated reports are based on RDL technology which is standard report format in SQL Server Reporting Services. The tool for developing paginated report in Power BI ecosystem is Power BI Report Builder. The reports file type is RDL. You can currently publish Paginated reports only to a Workspace that is backed with a premium capacity. Download Power BI Report Builder from here.
You can now export the data direct from Power BI Desktop using my tool, Power BI Exporter. Read more here.
In some of my old posts, which are the most popular ones, I explained how to Export data Power BI Desktop or Power BI Service data to different destinations like CSV, Excel and SQL Server. In this quick tip I explain a very easy way to export the model data as a whole to either CSV or SQL Server with DAX Studio.
Daniil from XXL BI well explained this method, but I’d rather quickly explain how it works and add some more information.
After release 2.8 of DAX Studio, you can now quickly export the whole model to CSV and SQL Server in one shot.
