A Fine Slice Of SQL Server
Recently I was working on a customer’s data and one thing that was missed was when something was not selected the Calculation Group did not return any default Format String.
Below is how I solved this, with the tricky part being formatting of the measure to be displayed correctly.
This follows on from my previous blog posts with regards to Calculation Groups:
Power BI Visuals now support Custom Formatting for measures using Calculation Groups!
Click through for the process.
Comments closedGerhard Brueckl checks out the Apache Parquet connector in Power BI, reading from a Delta Lake:
“Apache Parquet is a columnar storage format available to any project in the Hadoop ecosystem, regardless of the choice of data processing framework, data model or programming language.”
However, Parquet is just a file format and does not really support you when it comes to data management. Common data manipulation operations (DML) like updates and deletes still need to be handled manually by the data pipeline. This was one of the reasons why Delta Lake (delta.io) was developed besides a lot of other features like ACID transactions, proper meta data handling and a lot more. If you are interested in the details, please follow the link above.
Click through for a demo.
Comments closedAaron Nelson has some new cmdlets for us:
I just wanted to give everyone a heads-up that a new version of the ReportingServicesTools module went out last week, and it includes 3 new PowerShell functions for working with Power BI reports on a Power BI Report Server (PBIRS) instance.
You can now Create (New), Get, & Start a CacheRefreshPlan of a Power BI report deployed to a PBIRS instance. For clarity, these only apply to reports using an Imported model, not those using Direct Query.
Click through for more detail.
Comments closedReza Rad gives us a method to modify linked entities in Power BI dataflows:
It is not possible to change a linked entity in a dataflow in Power BI. Linked entities has to be modified only in the dataflow in which they are created. However, sometimes, you need to do a small modification to the linked entity in a chained dataflow. There is a very simple trick, In this article and video, I’ll explain how you can do that.
Read on to see how.
Comments closedMatt Allington has a workaround for us:
DAX is a tricky language; on the surface it is deceptively simple, but under the hood it can quickly become complex and it can take many years to master. If you have ever typed a formula and crossed your fingers when you press Enter, then you know what I mean. If you are reading this article, you are no doubt already on your own DAX learning journey.
As you become more competent at DAX, you will start to write more complex formulas that behave differently depending on the filters in your visuals. One such example is the P&L report that I shared in 2020. This report contains quite a tricky formula. It was built slowly and methodically, one step at a time, until it was working as desired. This is the best way to write DAX. If you want to see how I do it, then go back to the article linked above and watch the video. While I recommend this as the best approach to writing formulas, the reality is that sometimes you will need to go back to a formula you wrote previously and debug that formula. Maybe it is not working correctly, or maybe you need to enhance it for some reason. Whatever the reason, today I am sharing with you my technique to debug complex formulas using tool tips.
Read the whole thing.
Comments closedTomaz Kastrun looks at some new functionality in Power BI which might interest data analysts:
Small multiples is a layout of small charts over a grouping variable, aligned side-by-side, sharing common scale, that is scaled to fit all the values (by grouping or categorical variable) on multiple smaller graphs. Analyst should immediately see and tell the difference between the grouping variable (e.g.: city, color, type,…) give a visualized data.
In Python, we know this as trellis plot or FacetGrid (seaborn) or simply subplots (Matplotlib).
In R, this is usually referred to as facets (ggplot2).
Read on for an example of this, as well as two other features, as well as how you might have worked with these ideas in Python and R.
Comments closedImke Feldmann continues pulling on a thread:
In a previous article I have described how to use Fuzzy merge to match misspelled data to valid categories. With the sample data at hand, all values could be matched to existing categories. But what can you do if there are some entries that cannot be matched to existing categories?
I would create an “unmatched” value to the categories table before loading to the data model and assign the “unmatched”-value to all unmatched entries. Then I would create a measure that counts the entries within the unmatched category. I would then create a data driven alert, based on this measure. That would trigger an e-mail to the person responsible for maintaining the list. Or, if responsibilities are a bit more complex, trigger a flow in Power Automate that could handle certain conditions to be followed as well.
Click through to see it in action.
Comments closedPaul Turley explains a choice:
When should you use dataflows vs regular Power Query? I didn’t jump on the dataflows bandwagon and struggled to adopt them at first. Frankly, Power Query is easier to use. The browser-based dataflows designer is quite impressive but it is not as responsive and convenient as the desktop app, so this is a bit of a trade-off. The power and value of dataflows becomes apparently when the business reaches a certain stage of data culture maturity.
Before we can address the question of whether to use Power BI Dataflows, conventional Power BI queries, or any other approach to source and transform data; we need to briefly review different options for orchestrating a Business Intelligence solution in the Microsoft cloud ecosystem.
Read on for a bit of architecture and the explanation.
Comments closedReza Rad shows how we can write DAX measures against a Power BI streaming dataset:
In Power BI, you can have a dataset with Imported data, DirectQuery, Live Connection, or Composite mode. You can build all of those types of Power BI datasets in the Power BI Desktop. However, there is a single type of dataset, which you can only build through the service, called the Streaming dataset.
A streaming dataset is for building reports with real-time response time. For example, if you want to build a Power BI dashboard that shows the room temperature as soon as captured by a temperature sensor. For this type of dataset, you send the data rows using Power BI REST API, which can be called using a custom C# application, or PowerShell scripts, or even from a Power Automate flow process.
Read on to see how.
Comments closedMarco Russo and Alberto Ferrari show off some new DAX syntax:
Introduced in December 2020, the DEFINE COLUMN statement lets you define a calculated column local to a query. The column is not persisted in the model, it exists only for the lifetime of the query. Apart from that, it is a calculated column in every sense of the term.
The extension of DAX with the capability to define calculated columns local to a query is needed in order to support composite models over Analysis Services (AS). There are no limitations in the use of the feature. For this reason, you can take advantage of local columns in any DAX query. We refer to calculated columns defined in a query as query calculated columns, or query columns for short.
Click through to see it in action. I like this idea a lot, though do read their note regarding performance, contrasting it with ADDCOLUMNS.
Comments closed