How to get multiple slicer items filtered using the DAX TREATAS function
Quite often I wanted to get the data a slicer quickly and easily where multiple items are selected. I then wanted to use the slicer items to filter my measure. The first thing that I did was to manually hard code the values to make sure that I was getting the correct values with the following measure below. EVALUATE ROW…
Power Query – Remove all characters before delimiter & after delimiter
Below is the Power Query Syntax where I wanted to move the characters before the delimiter. I must give credit to the Power BI Community who got me going with how to solve this. For reference: Solved: Rename all column headers removing part before to … – Microsoft Power BI Community This is an example of what my column name…
Configure OLS in Power BI using Tabular Editor to limit access to non-financial measures
What Object Level Security (OLS) allows me to do, is to define which tables or columns users can or cannot see. My example which I am going to detail below will show you how I will restrict a user from viewing sales data. The same user will be able to see Quantity amounts. This becomes really powerful because not all…
Power Query – Continuous Week on Week Comparison
Below is the Power Query syntax to allow for Week-on-Week comparison over multiple years. I have seen in the past Week-on-Week comparisons but one of the challenges is what happens when it overlaps years. Especially at the start of a year like it is now Feb 2021, the users want to compare week-on-week for the past 3 months. My challenge…
Changing the User Account in Excel when connecting to Power BI Premium/Per User or Azure Analysis Services
Recently I have been working where I have multiple accounts, and I wanted to test the different access to the accounts when connecting via Excel. I could not find any blog posts or videos on how to do this. In my blog post I detail how to achieve this. Connecting to the dataset using Excel The method below will work…
Setting the Default Format String with Calculation Groups in Power BI
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…
How to create DirectQuery (Composite) using an existing Power BI Dataset and a local Excel file
If you are looking how to use DirectQuery (Composite) models in Power BI I show you how to do this with an existing Power BI dataset from the Power BI Service and then importing an Excel file from SharePoint. In my working example I already have got a dataset which contains the Power BI Audit Log data. I want to…
How to refresh Excel files faster in Power BI
I was recently getting an error when refreshing an Excel file that was stored in SharePoint online. This got me thinking what the issue is and is there a way to improve it and YES there is as detailed below. When I investigated this further, I found that my 17kb Excel file was using at least 616kb of data when…
You can now view the Power BI REST API calls via the Audit log
When I review the Power BI Audit logs, I am always finding either new operations or new additions into the audit log. In my example below I found a way to quickly and easily identify Power BI REST API calls that are being used. If you have not read my prior blog post where I detail how to store the…
How I changed the partition configuration (Year, Month, Quarter, Days) for my data in Power BI Premium / Power BI Premium Per user!
I have to say that I am quite fortunate to be a Microsoft MVP, and I am very grateful for the opportunity. This was one of those times when I was reading an email from Christian Wade and Christian had given me a little piece of gold. What is that gold you might wonder, well it is that there is…