After 14 years using Microsoft On Premise BI Tools (SQL Server, Reporting Services, Integration Services and Analysis Services) Its time to embrace Business Intelligence in the cloud.
This function checks whether a value is a logical value (TRUE or FALSE), and returns TRUE or FALSE.
I just looked a question that asks, what does this DAX expression do?
is logical measure = IF(ISLOGICAL("true"),"Is boolean or Logical","Is different type")
I could just have a guess based on If “true” is logical then “Is Boolean or Logical”. Else “Is different type”.
Well, We have added “True” true within quotation marks which makes it text, not Boolean or logical.
As expected. Lets change the DAX
is logical measure = IF(ISLOGICAL(true),"Is boolean or Logical","Is different type")
True is Boolean because you can only have True or false
is logical measure = IF(ISLOGICAL(false),"Is boolean or Logical","Is different type")
Still Logical because its Boolean so the card stays the same
So why would you use ISLOGICAL?
You would mostly use this to test data so its great for making sure columns of true and false only contain boolean true and false information.
So, you always need to test how good your data is. Add an ISLOGICAL for true /false columns and you can report back on the validity of the data.
Salaried Flag in Adventureworks is a boolean column
Here is where the confusion is. You cant feed Salaried flag into this because we have created a measure and salaried flag is not an aggregation. You cant aggregate true or false
So instead I create a calculated column on the Employee table
Is Salary flag true or false? = IF(ISLOGICAL(DimEmployee[SalariedFlag]),"Is boolean or Logical","Is different type")
Great, Salaried flag has passed the test.
So ISLOGICAL, great for testing within a calculated column
I want to set up Power BI Dedicated Capacity specifically for development and testing Premium features.
the Power BI Premium SKU’s should only be used in production. We want something that we can pause when not in use and help us to research all the Premium capabilities.
Thankfully we can go for the A SKU (1 through 6)
A4 provides the same functionality as the P1 SKU
A2 is the same as P2
A6 is the same as the P3
The only difference is that you don’t get full user access like you do with Premium. Only users with Pro accounts can access the content (Which makes sense because we haven’t moved to Production)
And you can Pause when not it use. This is fantastic and will save lots of money in the development and testing phases
The question is, which SKU do we require. I know I need to test Paginated Reports and this is only available from A4 so clearly we need the SKU that corresponds with P1
Buying A4 SKU
Using an account that has at least capacity admin permissions in Power BI.
Log into Azure and Search for Power BI embedded
Here I have decided to choose our training subscription and training resource group.
 The capacity admin must be a member user or a service principal in your Azure AD tenant. I have selected myself for this role for this development Power BI area
At this point you need to choose the resource size and I know that we need the A4
If we left this one the whole time it would cost £4,467.41 for the month but obviously we only need this when we are testing Premium functionality so we would be pausing this most of the time.
Its extremely important that this is paused after use
I have added a few tags to this service before creating
Go to resource because we want to immediately Pause it
And Pause. I will only unpause the resource when I want to set up an App Workspace on this test capacity to check the Paginated report and other functionality.
The A4 SKU Will cost around £6 an hour so ensure you plan your development time effectively
I wanted to have a look at What if Parameters in Power BI. This had passed me by so using Adventureworks I wanted to get a handle on it.
I have a simple problem. I have a sales goal Id like to achieve over the months and Id like to see how adding percentages onto sales would help me hit my goal.
For example, If I add 1% onto sales, would that help hit the targets?
Management have decided that we need to be selling £170M each month as their big sales target after analysing current sales
The first thing we do is add this into the report using a constant line
We want to know how to set Sales Quotas, what do we need to do to push towards the targets. From Modeling, select the What if New Parameter
This is all fairly standard stuff. We want to increase by percentages for the sales forecast so we use a Fixed Decimal number. Then we want to be able to see changes from 1% to 150%
We want to go up in increments of half a percent
Now we have a slicer which we can move to choose percentage increases and a new table called % Sales Forecast.
If you go into data view you can look at the table created and see that this has created our calculated column of data for the forecast (Single Column table)
this is the measure that has been created in the table so it doesn’t exist in the table structure. It returns the value, in this case when % Sales Forecast calculated column has been filtered down to one distinct value
Create a measure in new % sales Forecast table to take sales and multiply by the measure
For training courses and general lets have a look at updates to Power BI, an Adventureworks database instance has been set up on my machine, complete with 10 years of data that is great for using for training courses.
However, this database needs moving from its local machine to Azure
Previously bacpac files have been created to import into Azure via SQL Server Management Studio but they have always errored.
Its time to have a look at Azure Data Migration
Source Database
Product – Microroft SQL Server Express (64bit)
Operating System – Windows 1 Pro
Version: 14.0.2027.2
Release: SQL Server 2017
Attempt 1
Microsoft Data Migration Assistant
The Microsoft Data Migration Assistant allows you to upgrade to Azure by detecting compatibility issues.
The first thing to do is to download the msi package and then you can get started
Open the Data Migration Assistant
Click New to create a new Project
Then click create
The server would be connected to using Windows Authentication. However there are errors for this. the credentials must have control server permission.
In SSMS go to Security and Logins to find your own user Login.
Switch to the master database and run the following SQL
GRANT CONTROL SERVER TO [User Name];
Unfortunately the error comes back Securable class ‘server’ not supported in this version of SQL Server.
SSMS is not able to Migrate Express Databases
Attempt 2
b
Move to your Azure connection in SSMS and right click on databases Import Data Tier Application
go through the Import Data Tier guide
Currently we are moving to an S2 model. (Purchase model DTU) which is around £54 a month. When its not being used I will change it to S0, around $10 a month.
There are compatibility issues happening between the Azure database and the SQL Express data base.
The first is regards to COLUMNSTORE. Apparently COLUMNSTORE support is available in S3 and above
S3 costs around £109 pounds a month, twice the cost of S2.
The Solution
Simply Import as S3 and then let Azure Scale to another Standard option by clicking on Pricing tier
You could also move to vCore purchasing options too which is the preferred route as it offers more personalisation.
The DTU-based purchase model are differentiated by a range of compute sizes with a fixed amount of included storage, fixed retention period for backups, and fixed price.
The virtual core (vCore) model provides several benefits:
Higher compute, memory, IO, and storage limits.
Control over the hardware generation to better match compute and memory requirements of the workload.
I can finally move my Express database into Azure and manage the scaling. All sorted.
However you should be aware that if its Under S3 the index gets disabled and Power BI cant use it in this state
So the question is, If you are importing the data into power BI, will you be happy to keep the database at S3 level when its in use OR would be be OK with removing the columnstore index?
An Azure service principal is a security identity used by user-created apps, services, and automation tools to access specific Azure resources. Think of it as a ‘user identity’ (login and password or certificate) with a specific role, and tightly controlled permissions to access your resources
I am constantly having to remind myself how to set up the Service Principal for Access to things like Azure Data Lake Gen 2 when I am setting up a data factory (Or using the storage with another web app).
So I wanted to write a blog post specifically on this.
As the example, imagine you are moving data from an Azure SQL Database to files in Azure Data Lake Gen 2 using Azure Data Factory.
You attempt to add a Data Lake Connection but you need a Service Principal account to get everything Authorised.
You need this so the Data Factory will be authorised to read and add data into your data lake
An application (E.g. data Factory) must be able to participate in a flow that requires authentication. It needs to establish Secure credentials. The default method for this is a client ID and a Secret Key.
There are two types of permissions
Application Permissions No user context is required. The App (E.g. data Factory) needs to access the Web API By its self
Delegated Permissions The Client Application (E.g. data Factory) needs to access the Web API as a Signed in User.
Create an App
In Azure choose App Registrations
Here you can create an app – New Registration
Provide a name for your app. e.g. DataFactoryDataLakeApp
Grant your Registered App permissions to Azure Storage
This will enable your app to authorise Requests to the Storage Account With Azure Active Directory (AD)
You can get to your app by going to Azure Active Directory
Then App Registrations and choose the App
In your new App, go to Overview and View API Permissions
Next go to Add a permission
Go to Azure Storage API which contains Data Lake Gen 2
Notice that we are setting up Delegated Permissions for Azure Storage
You are warned that Permissions have been changed and you need to wait a few minutes to grant admin consent.
I am not an admin so I always get my admin to go into Azure Active Directory and Grant Admin Consent for Peak Indicators
Note that your app now has configured permissions for Azure Active Directory Graph and Azure Storage
Assign your new app to a subscription
Now you have an app you need to assign Contributor status to it to the level of service you require in Azure, Subscription level, Resource group level or resource level.
For this app I am going to set it up against the subscription. First go to the Subscription you want to add it to and then Access Control (IAM)
I have added the app as a contributor
Creating a Key Vault
We will be selecting and creating IDs in the next steps, but instead of simply remembering your secret. Why not store it in a Key Vault.
Centralise Application Secrets
Store Secrets and Keys Securely
Monitor Access And Use
Lets set one up in our Proof of Concept area.
Create a Key vault if you don’t have one already
remember to add any tags you need before Review + Create
Once completed you can go to the resource (E.g. Data Factory) but for the time being that is all you need to do
Application ID and Tenant ID
You can now go into your new app in Azure (App registrations) to get more details for Data Factory (When you set up the connection)
Tenant from Data Factory will be mapped to Directory (Tenant ID) from the App Overview
Service Principal ID from Data Factory will be mapped to Application (Client) ID From the App Overview
Create a Client Secret
Next, create your Client Secret.
In your App go to Certificates and Secrets
Click New Client Secret
Im going to allow this secret to Expire in a year (Anything using the app will start to fail so you would need to set a new secret and re-authorise)
We can add this into the Key vault so we don’t lose it because once you have finished here you dont see it again.
Open a new Azure Window and Go to your new Key Vault
Go to Secrets
Click + Generate Import
Notice I have set the expiration date to match the expiry date of the app
Ensuring the Access is set for the Data Lake Storage
Finally, something we have been waiting for for a long time.
Its amazing how such a little thing can case so much extra complexity.
Previously you could only show this on the KPI
The Goal States 1749 but this isnt a Goal. This is Last Months measure. Not a goal
This basically means that for the KPI you can only use them in this way if your data makes sense for there to be a Goal. Maybe you might have to add more information about the Goal in the Visual Header Tool tip icon.
However, for my report I need it to be stated that this is last month figure as compared to this months. I have lots of KPIs based on Current and this time last month so I had to create them like this
Instead of 1 visual I have 2. A KPI and a card, Hiding Goal in the KPI. this creates a lot more complexity in the reports but I had to do it to avoid Confusion.
Now, we can reset the Goal to whatever we want
Finally I can reset all my reports so I can reduce the amount of visuals shown.
Such a small change but it makes a big difference.
Thank goodness for the amazing people on the Power BI Forums helping with this. I have the solution but what I wanted to do was to create a post to explain the why
The Problem
I have accounts and events occurring on each account. What I want to know is the time period they are open
Just for this example I have taken One account with 11 activities
If you run the visual against start date all you get is a measure againt the start time period.
We are more interested in creating a measure to show how long the activity went on for.
For this example I am going to choose Jan 28 2017 to work with to continue because the end date for this is March 21st 2017 so this activity should span 3 months
The Date Dimension
To do this you must have a date dimension. For the purposes of this example I’m going to create one in DAX (Create table) Using the following:
Notice that I have used Start and End Dates to set the min and Max years. You should already have a date dimension to work with though.
Set up the relationship between the Fact table and Date dim
Start date is the Active join and End Date is the Inactive Join
You could create a role playing dimension for the End date and have that as an active join but in this case I’m going to go for the above design.
Account Start flag (New Measure)
CALCULATE – You can modify your expression using a filter
ALLSELECTED – If you have a slicer set to Year (Date) , ALLSELECTED takes the ‘Context’ from the slicer Like 2018.
Date is less than or equal to the Max Date in the Filter Context
Account End Flag (New Measure)
ENDOFMONTH – Returns the last date of the month in the current context for the specified column of dates.
The date at the end of the month is less than the Max date in the Filter Context
USERELATIONSHIP Because End Date is the inactive relationship we need to specify that this relationship is used, Not Start Date
Add an Open Measure
To understand the results we need to try and understand how the new measures work
Jan
Start Flag =Â Â Â Â Â Â Â 1 because 18th Jan is less than Jan 31st
End Flag  =       NULL because March 21st (Reset to March 31st) is NOT Less than Jan 31st
Open =Â 1- null = 1
Feb
Start Flag =Â Â Â Â Â Â Â 1 because 18th Jan is less than Feb 28th
End Flag  =       NULL because March 21st (Reset to March 31st) is NOT Less than Feb 28th
Open =Â 1- null = 1
Mar
Start Flag =Â Â Â Â Â Â Â 1 because 18th Jan is less than Mar 31st
End Flag  =       NULL because March 21st (Reset to March 31st) is NOT Less than March 31st
Open =Â 1- null = 1
Apr
Start Flag =Â Â Â Â Â Â Â 1 because 18th Jan is less than April 30th
End Flag  =       1 because March 21st (Reset to March 31st) is Less than April 31st
Open =Â 1- 1 = 0
There are usually lots of ways to answer one question so this is one of many but the Open Flag now allows me to pull up all my activities and show in a Matrix where the are open.
Debbies Microsoft Power BI, SQL Fabric and Azure Blog 