A Fine Slice Of SQL Server
Gilbert Quevauvilliers walks us through the groups which can set permissions in Power BI:
As you can see from above it is good to know which groups can be used to assign permissions in the Power BI Service.
If there is anything I have missed, is wrong or needs updating please let me know via the comments section below.
Thanks for reading!
Gilbert has a nice matrix as well as lots of screenshots establishing the matrix’s veracity.
Comments closedReza Rad comes up with an interesting hack for Power BI:
I have a table for all customers, and I am showing them all in a table visual in Power BI. However, there are many customers in the list, let’s say 18K+. If I want to search for all customers who have “q” in the name, then I need to either scan the table myself, Or use a slicer with a search box, and search for character “q”, and then select all the names with “q” one by one! something like below is tedious!
Click through for more details and the opportunity to download a sample file.
Comments closedCedric Charlier shows how to turn a running total into a periodic series of events with M:
When dealing with time series, you should always check if your time series are event-based or cumulative. For a time series with a step of one day, an event-based time series will contain the count of events for a given day. A cumulative time series will contain the sum of the event of that day and of all the previous days! In this blog post I’ll explain how to transform a cumulative time series into an event-based.
Click through for the code. You can do this in T-SQL as well by subtracting the value from its LAG()-ged value.
Soheil Bakhshi has an interesting problem to solve:
Now, the requirement is to show the above behaviour in a “Column Chart” (yes! visualising time series with column chart, that’s what the customer wants) and highlight the columns with values below average amount in Orange and leave the rest in default theme colour.
So, I need to create Measures to conditionally format the column chart. I also need to add a bit of intelligent in the measures to:
– Detect which hierarchy level I am in
– Calculate the average of sales for that particular hierarchy level
– Change the colour of the columns that are below the average amountLet’s get it done!
Read on to see how you can do exactly this.
Comments closedReza Rad takes us through the GroupBy function in DAX:
There are many different ways you can create aggregations in Power BI, You can do it in the source (using the database t-SQL language), or using Group By operation in Power Query. You can also do it in DAX using some functions. One of the functions that can be used for grouping and aggregation is Group By. This article is about how to use Group By in DAX. Creating aggregation using DAX is a very useful skill because you can use it to create virtual tables in your measures and have better dynamic calculations in Power BI.
Click through for the explanation and an example.
Comments closedChris Webb has a public service announcement:
A very quick performance tip: do you have users consuming Power BI reports with Internet Explorer 11 and an older Windows OS like Windows 8.1 or Windows 7? If so, their reports are likely to be slower[…]
Click through to see why they’re slower and what you can do about it. Other than upgrade to Windows 10 or use a different browser.
Comments closedPhil Seamark explains what the KEEPFILTERS() function does in DAX:
If you ever spend time looking at DAX generated by the Power BI Desktop Performance Analyser, you may notice a function called KEEPFILTERS appear from time to time. It’s not a function I find myself using often, so I thought I’d do a bit of digging to find out what it does and when it might be useful.
Read on to learn exactly what it does.
Comments closedCedric Charlier shows off some of the benefit of Power Query with a fairly complicated set of rules:
At the beginning, some of us thought that it would be easy to fix these issues by returning to the data quality team and ask them to fix these issues but it was not so easy. Identifing the rules needing a fix would be huge task (the CSV files are not created if the test is successful, maling it impossible to address this issue in one run and other impediments). I took the decision to go over this issue with the implementation of the following heuristic:
– if the CSV has a column DateTime then we’ll use it
– if the header is empty or no column is named DateTime then use the first column
– if the content of the selected column is not a date then try to parse it as the inner content of a JSON element.
Read on to see how.
Comments closedWhen cleaning dirty data, you might have used the Trim-function (Text.TrimStart or Text.TrimEnd) to delete leading or trailing whitespace from your strings. However, did you know that you could use these functions as well to delete any other characters as well from the start or end of a string? Trimming text with custom characters is pretty straightforward:
Interesting that it does not take a regular expression but rather a list of characters.
Comments closedPatrick LeBlanc has a video for us:
Are you not sure if you are hitting your Power BI Aggregations? Patrick shows you some tools you can use to verify if they are being used.
Click through for the video.
Comments closed