A Fine Slice Of SQL Server
Chris Webb blows out the margins:
Now you can easily extract the individual property values from this column in KQL, and indeed I did so in the KQL queries in this post, but the interesting thing is you can also extract these values in Power Query M very easily and – crucially – maintain query folding using the Record.FieldOrDefault M function in a custom column without needing to write any KQL yourself, in both Import mode and DirectQuery mode.
Read on to see how.
Comments closedGerhard Brueckl avoids manual processes:
In the past I have been working on a lot of different Power BI projects and it has always been (and still is) a pain when it comes to the deployment of changes across multiple tiers(e.g. Dev/Test/Prod). The main problem here being that a file generated in Power BI desktop (
.pbix) is basically a binary file and the metadata of the actual data model (BIM) cannot be easily extracted or derived. This causes a lot of problems upstream when you want to automate the deployment using CI/CD pipelines. Here are some common approaches to tackle these issues:
Click through to see several bad to palatable options and then check out Gerhard’s solution, which is significantly better. CI/CD is a huge pain point for Power BI developers but people like Gerhard are doing what they can to help.
Comments closedGilbert Quevauvilliers wants to see who’s using what tables:
I was reading through the blog post Announcing on-demand loading capabilities for large models in Power BI and I got a thought would it not be great to better understand which columns and tables are being used in my Power BI Premium/Premium per user datasets?
To do this, using the new DMV I could now look at the temperature of the tables-column.
The higher the temperature the more the table-column is being used in my reports!
Click through to see how Gilbert put this together but also pay attention to the caveats.
Comments closedMarco Russo and Alberto Ferrari are linguists:
When calculation groups were introduced in 2019, we did not have a way to describe them in a textual form. A calculation group was represented as a table with one visible column and one or more rows, one for each calculated item. Each calculation item could have one or two DAX expressions associated with it – one for the calculation item itself and an optional one for the format string. Describing a calculation group in an article often required the writer to include screenshots of the Tabular Editor user interface, plus comments in the sample code to explain where each DAX expression should be placed in the user interface.
From the start we proposed a syntax to describe an entire calculation group in a textual form. However, there was no tool able to convert that syntax into the actual object in the Tabular model. For this reason, in the initial version of the articles about calculation groups we used a “pseudo-syntax” and we included comments that made the code more verbose and not necessarily easier to read. However, Tabular Editor 3 introduced the full DAX script syntax for calculation groups that we had hoped would be available in 2019. We decided to adopt that syntax in our content. We use this article as a guide to introduce and explain the DAX Script syntax for calculation groups.
Go check it out.
Comments closedMarc Lelijveld continues a series on Power BI workspace configuration:
After Power BI Workspace setup – part 1, which was mainly about creating your workspace, giving it the right name etcetera, this blog will elaborate on workspace permissions. If you did not read part 1 yet, I encourage you to start at that blog to get the basic setup in place.
You might think, permissions is an easy topic, but often underestimated! In this blog I will describe the different workspace roles, and how you can apply them in your project. Also we will look at sharing specific content from a workspace perspective, such as sharing dataflows.
Read on to learn more.
Comments closedReza Rad has the right price in mind:
A question I often get from many students is: “How can I practice Power BI service features if I do not have a Power BI Account?”. Not having a Power BI account can happen because of many scenarios; your company might close this option so that the process be only channeled through a specific process within the company. Or you may not have the permission to do so. Not having an account makes it difficult to practice Power BI Service options such as workspace, datasets, dashboards, dataflows, apps, and many other features. On the other hand, even if you have the Power BI Service account, in most of the organizations, you are not the service administrator, so you cannot practice tenant-settings configurations in the service.
Fortunately, there is a way to create your own Power BI sandbox; which means an environment just for yourself, with 25 accounts. You will be the administrator of your environment. The environment will be up for at least 90 days, and you can practice whatever you want for the Power BI service there. The best of all, it is FREE. You don’t have to pay a cent for it. Credit card detail is not needed. What better you could wish for?
Read on to see how.
Comments closedChris Webb continues a series on Power BI monitoring:
In my last post I showed how to use Log Analytics data to analyse Power BI query activity. The problem with looking at a long list of queries, though, is that it can be overwhelming and it can be hard to get a sense of when users were and weren’t actively interacting with a report. In this post I’ll show you how you can write a KQL query that gives you a summary view that solves this problem by grouping queries into sessions.
Click through to see what Chris means by the term “session” and for the KQL to do the job.
Comments closedGilbert Quevauvilliers shows off the Power BI Embedded Playground:
One of the great things about Power BI is how they make things a lot easier and better to use.
I have been answering some questions in the Power BI Community and other people have been looking into using Power BI Embedded.
This led me to find out that there is a quick and easy way to test out Power BI Embedded. The best part is that I can use my own reports and do a drag and drop test!
Click through to see how.
Comments closedAdam Aspin shows us how to use DAX functions in Power BI paginated reports:
In the previous articles, you learned – or revised – the basics of using DAX as the query language to populate paginated reports with data from Power BI datasets. However, as befitted an introduction, the focus was essentially on getting up and running. Specifically, the only DAX table function you looked at was
SUMMARIZECOLUMNS().Despite its undeniable usefulness, this function is far from the only DAX function that you can use to query Power BI Datasets when creating Paginated Reports. Moreover, it has limitations when you wish to deliver complete lists of results as it is an aggregation function. This means, for instance, that you will never find duplicate records in the tabular output from
SUMMARIZECOLUMNS()as, by default, it is grouping data. Alternatively, if you wish to useSUMMARIZECOLUMNS()to output data at its most granular level, you will need to include a unique field (or a combination of fields that guarantee uniqueness) – even if these are not used in the report output.It follows that, to extract data in ways that allow effective report creation, it is essential to learn to use a whole range of DAX table functions.
Click through for a list of functions and how to use them.
Comments closedChris Webb continues a series on using Log Analytics:
It’s actually very easy to build a simple KQL query to look at query activity on your datasets: you just need to look at the QueryEnd event (or operation, as its called in Log Analytics), which is fired when a query finishes running. This event gives you all the information you need: the type of query (DAX or MDX), the duration, the CPU time, the query text and so on. The main challenge is that while you also get the IDs of the report and visual that generated the query, you don’t get the names of the report or visual. I wrote about how to get a list of visual and report IDs here and here, but how can you use that information?
Read on to see how.
Comments closed