BLOGS

DAX – Adjust the Total value – Conditional Total in DAX

In this article, I want to show you how I achieved a business solution using DAX. Let me explain the requirement.

I have a below table and customer wants the data in the below format.

 Country Color Quarter Product India Green 2019-Q1 P1 USA Blue 2019-Q1 P2 UK Red 2019-Q1 P3 India Red 2019-Q2 P1 USA Blue 2019-Q2 P2 UK Amber 2019-Q2 P4

Expected Solution

 Country 2019-Q1(Green & Blue) 2019-Q2 (Green & Blue) India 100% 0% USA 100% 100% UK 0% 0% Total 67% 33%

Actually, the customer wants to show the count as 1 or 0 if green or blue exists on each country so the required result would be like below.

 Country 2019-Q1(Green & Blue) 2019-Q2 (Green & Blue) India 1 0 USA 1 1 UK 0 0

First step: we can target blue and green count.

Green_Blue = CALCULATE(

COUNTROWS(‘Product’),

‘Product'[Color] in {“Blue”,”Green”})

The next upgraded version is handling blank with zero.

GB =

Var GBVal = CALCULATE(COUNTROWS(‘Product’),’Product'[Color] in {“Blue”,”Green”})

Return

if(GBVal = BLANK(),

0,

GBVal)

Next level, we can divide the value to get the percentage.

GB =

Var GBVal  = CALCULATE(COUNTROWS(‘Product’),

‘Product'[Color] in {“Blue”,”Green”})

Return

if(GBVal  = BLANK(),

0,

GBVal  / CALCULATE(

COUNTROWS(‘Product’),’Product'[Color] in {“Blue”,”Green”},

all(‘Product'[Quarter])))

Then finally, we need to tweak the result on the divide section to get the expected result. This will adjust the total section.

GB =

Var GBVal = CALCULATE(COUNTROWS(‘Product’),

‘Product'[Color] in {“Blue”,”Green”})

Return

if(GBVal = BLANK(),

0,

GBVal / if(

DISTINCTCOUNT(‘Product'[Country])>1,

CALCULATE(COUNTROWS(‘Product’),’Product'[Color] in {“Blue”,”Green”},

all(‘Product'[Quarter])),GBVal))

We got the expected result. Please provide your feedback and comments.

Power BI Shared Datasets – Cheat Sheet

In this article, I want to show you the cheat sheet of the Shared Dataset inside Power BI. I tried to cover all the information related to shared dataset.

As you know, the Shared dataset is one the latest update from Power BI team which opens a new channel to data analyst & business users to create reports from the available datasets. It reduces their work on building the data model from scratch.

It also helps the organization to maintain a single version of the truth on data.

DAX – Top N for each Previous & Current Year

This article covers a real-time scenario which was asked by one of the customers.

Requirement:

Show the sales of the top 5 countries for the current year and previous year.

Challenges:

The above requirement seems to be simple, but we don’t have the same customers on the previous and current year.

1. If we use a simple bar/column chart, then we will get multiple standalone bars or columns.
2. We can use a stacked column chart but if we apply the top 5 then it will apply on the whole dataset, but we need to apply for each year.

Solution:

Follow the below steps

1. Create a DAX table with below logic
1. TOP 5
2. Filter – Current Year & Previous Year
3. Union the above results

Detailed Steps:

I am using below dataset (Sales).

 Country Year Value A 2018 12 B 2018 34 C 2018 56 D 2018 17 E 2019 17 F 2019 19 G 2019 11 H 2019 23 I 2017 16 J 2017 18 K 2017 23 L 2017 26 M 2017 56

Used the DAX syntax to create another table

_Top5 = UNION(

CALCULATETABLE(

TOPN(5,

SUMMARIZE(Sales,Sales[Year],Sales[Country],Sales[Value])

,Sales[Value], DESC),

Sales[Year]=FORMAT(YEAR(TODAY()),”0000″)),

CALCULATETABLE(

TOPN(5,

SUMMARIZE(Sales,Sales[Year],Sales[Country],Sales[Value])

,Sales[Value], DESC),

Sales[Year]=FORMAT(YEAR(TODAY())-1,”0000″))

)

Now, create a stacked column chart which will give the expected result.

post your comment, if you have any other approaches.

Power BI Inventory using Power BI REST API

This blog post explains how the REST API can be used to prepare the Power BI Inventory Report. On this inventory report, we can show below items which almost covered all the contents from Power BI.

1. List of Workspaces
2. List of Datasets
3. List of Reports
4. List of Dashboards
5. List of Tiles
6. List of Data Gateways
7. List of Data sources on the gateway
8. List of users and their access.

Those REST APIs are wrapped into PowerShell script and exporting the result as csv files. You can schedule this PowerShell to get the latest contents from Power BI on a daily basis.

Pre-Requisite:

1. Register an app and get the Client ID and Use it on the below script

`[sql]`

# Parameters – fill these in before running the script!

# ======================================================

# AAD Client ID

# To get this, go to the following page and follow the steps to provision an app

# https://dev.powerbi.com/apps

# Level of access: check all boxes

\$clientId = “xxxxxx-xxxx-xxx-xxx-xxx”

# End Parameters =======================================

function GetAuthToken

{

if(-not (Get-Module AzureRm.Profile)) {

Import-Module AzureRm.Profile

}

\$redirectUri = “urn:ietf:wg:oauth:2.0:oob”

\$resourceAppIdURI = “https://analysis.windows.net/powerbi/api”

\$authContext = New-Object “Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext” -ArgumentList\$authority

\$authResult = \$authContext.AcquireToken(\$resourceAppIdURI, \$clientId, \$redirectUri, “Auto”)

return \$authResult

}

function get_groups_path(\$group_id) {

if (\$group_id -eq “me”) {

return “myorg”

} else {

return “myorg/groups/\$group_ID”

}

}

# PART 1: Authentication

# ==================================================================

\$token = GetAuthToken

# Building Rest API header with authorization token

‘Content-Type’=’application/json’

}

# ==================================================================

# Get the list of groups

\$uri = “https://api.powerbi.com/v1.0/myorg/groups/”

\$all_groups = (Invoke-RestMethod -Uri \$uri –Headers \$auth_header –Method GET).value

\$filePath = (“C:\Users\Hari\Documents\Allgroups.csv”)

\$all_groups | Export-csv \$filePath

# ==================================================================

# Get the list of gateways

\$uri = “https://api.powerbi.com/v1.0/myorg/gateways/”

\$all_gateways = (Invoke-RestMethod -Uri \$uri –Headers \$auth_header –Method GET).value

\$filePath = (“C:\Users\Hari\Documents\AllGateways.csv”)

\$all_gateways | Export-csv \$filePath

# ==================================================================

# Get the list of gateways data sources

\$uri = “https://api.powerbi.com/v1.0/myorg/gateways/”

\$all_gateways = (Invoke-RestMethod -Uri \$uri –Headers \$auth_header –Method GET).value

\$all_gateways_id=\$all_gateways.id

foreach (\$gateway in \$all_gateways_id)

{

\$uri = “https://api.powerbi.com/v1.0/myorg/gateways/\$gateway/datasources”

\$all_gateway_DS = (Invoke-RestMethod -Uri \$uri –Headers \$auth_header –Method GET).value

}

\$filePath = (“C:\Users\Hari\Documents\AllGatewaysDS.csv”)

\$all_gateway_DS | Export-csv \$filePath

# ==================================================================

# Get the list of datasets from Groups

\$all_datasets=@()

foreach (\$group in \$all_groups)

\$group_id = \$group.id

\$uri = “https://api.powerbi.com/v1.0/myorg/groups/\$group_id/datasets”

\$datasets = (Invoke-RestMethod -Uri \$uri –Headers \$auth_header –Method GET).value

\$all_datasets+=\$datasets

}

\$filePath = (“C:\Users\Hari\Documents\alldatasets.csv”)

\$all_datasets| Export-csv \$filePath

# ==================================================================

# Get the list of reports from Groups

\$all_reports=@()

foreach (\$group in \$all_groups)

\$group_id = \$group.id

\$uri = “https://api.powerbi.com/v1.0/myorg/groups/\$group_id/reports”

\$reports = (Invoke-RestMethod -Uri \$uri –Headers \$auth_header –Method GET).value

\$all_reports+=\$reports

}

\$filePath = (“C:\Users\Hari\Documents\allreports.csv”)

\$all_reports| Export-csv \$filePath

# ==================================================================

# Get the list of dashboards from Groups

\$output_dashboard = foreach (\$group in \$all_groups)

\$group_id = \$group.id

\$uri = “https://api.powerbi.com/v1.0/myorg/groups/\$group_id/dashboards”

\$all_dashboards = (Invoke-RestMethod -Uri \$uri –Headers \$auth_header –Method GET).value

New-Object -Typename PSObject -Property @{

Group_Id = \$group.id

GroupName = \$group.name

displayName = (\$all_dashboards.displayName -join ‘,’)

} | Select-Object Group_Id,GroupName, DisplayName

}

\$filePath = (“C:\Users\Hari\Documents\alldashboards.csv”)

\$output_dashboard| Export-csv \$filePath

# ==================================================================

# Get the list of tiles

\$all_tiles=@()

foreach (\$group in \$all_groups)

\$group_id = \$group.id

\$uri = “https://api.powerbi.com/v1.0/myorg/groups/\$group_id/dashboards”

\$all_dashboards = (Invoke-RestMethod -Uri \$uri –Headers \$auth_header –Method GET).value

foreach (\$dashboard in \$all_dashboards)

{

\$dashboard_id = \$dashboard.id

\$uri = “https://api.powerbi.com/v1.0/myorg/groups/\$group_id/dashboards/\$dashboard_id/tiles”

\$tiles = (Invoke-RestMethod -Uri \$uri –Headers \$auth_header –Method GET).value

}

\$all_tiles+=\$tiles

}

\$filePath = (“C:\Users\Hari\Documents\alltiles.csv”)

\$all_tiles| Export-csv \$filePath

# ==================================================================

# Get the list of users from Groups

\$output = foreach (\$group in \$all_groups)

\$group_id = \$group.id

\$uri = “https://api.powerbi.com/v1.0/myorg/groups/\$group_id/users”

\$all_group_user = (Invoke-RestMethod -Uri \$uri –Headers \$auth_header –Method GET).value

New-Object -Typename PSObject -Property @{

GroupName = \$group.name

Access = (\$all_group_user.groupUserAccessRight -join ‘,’)

DisplayName = (\$all_group_user.displayName -join ‘,’)

PricipalType = (\$all_group_user.principalType -join ‘,’)

} | Select-Object GroupName,Access, DisplayName, PricipalType

}

\$filePath = (“C:\Users\Hari\Documents\AllUsers.csv”)

\$output | Export-csv \$filePath

## ==================================================================

## Get the list of datasets from Groups with Group Names

\$alldatasets_GP = foreach (\$group in \$all_groups)

\$group_id = \$group.id

\$group_name = \$group.name

\$uri = “https://api.powerbi.com/v1.0/myorg/groups/\$group_id/datasets”

\$alldatasetsGP = (Invoke-RestMethod -Uri \$uri –Headers \$auth_header –Method GET).value

New-Object -Typename psobject -Property @{

GroupId = \$group.id

GroupName = \$group.name

Dataset_Id = (\$alldatasetsGP.id -join ‘,’)

DisplayName = (\$alldatasetsGP.name -join ‘,’)

} | Select-Object GroupName,Dataset_Id, DisplayName

}

\$filePath = (“C:\Users\Hari\Documents\alldatasets_Groups.csv”)

\$alldatasets_GP | Export-csv \$filePath

`[/]`

Recover & Restore Deleted Power BI Workspace (Step by Step)

In recent times, I encountered one of the most common questions is how to recover the Power BI workspace and how to restore the deleted workspace in Power BI?.

I thought it would be very helpful if I write a blog post to answer those questions. This content is applicable for Power BI new workspaces.

Recover and Restore are two different concepts. Let us try to understand the difference first.

Restore – If you deleted an object which you want to get it back

Recover – The live object went to not responding stage or orphaned stage and getting that object back to live.

In Power BI,  Most of the time, you will encounter restore scenarios.

To perform the restore and recover operations, you need admin rights in Power BI.

Restore Operation

Let us consider a scenario, one of your team member working on maintenance activity and unfortunately, he deleted one of the business app workspaces as an admin you want to restore it back.

Follow the below steps

Step 2: Check for workspaces section

Step 3: Check for the workspace on the list and check the State of the workspace.

Step 4: Choose the deleted workspace

Step 5: You can see an option called “Restore” on the top. Click Restore.

Step 6: Add at least one user as an admin and click restore. Now, the workspace will be restored.

The contents on the workspace also will be restored.

Recover Operation

To understand recover workspace, we need to know the different states of the workspace. Usually, we will be seeing the following states Active & Deleted.

Active – The workspace is live, and users can access the content from the workspace

Deleted – The workspace is deleted and can be restored if you want. Once restored then workspace will change to Active state.

Other than the above we have another state called “Orphaned”. If the workspace is changed to orphaned then you to recover workspace otherwise it will not visible to the users and can’t access that workspace.

Let me explain to you how to make the workspace orphaned but you don’t try at your end.

Whenever you create a workspace, it will consider the workspace creator as an admin of the workspace. It is always mandatory that at least 1 user should be admin of the workspace. You can check the access once the workspace is created.

Sometimes you might encounter the below error message. It means you or your admin team member removed the admin of that workspace.

If any workspace doesn’t have an admin then it will change to the orphaned state.

In Power BI, you can’t remove the admin from the workspace but there is an option to remove an admin user from the workspace,

I don’t know why the product has this option. As per my understanding, this should be fixed. Let me show how to remove the admin users as well. Please don’t try with your production workspaces.

Follow the below steps

Step 2: Check for workspaces section

Step 3: Check for the workspace on the list and check the State of the workspace.

Step 4: Choose the orphaned workspace

Step 5: Click recover on the top. Recover option will be available only for orphaned workspace

Step 6: You need an admin user to the workspace, and it will be recovered.

Thanks

Power BI Dataflow – Cheat Sheet

This post will give you the overall information about Power BI dataflow.

A- Create app workspace to create dataflows

B – Click create and choose dataflow which will allow you to create entity. Create entity using Power Query online. If you use on-premises data then choose the gateway. Apply the transformations and create the entity and name the dataflow

C – Option to create computed entity – the main entity should be enabled load. IN computed entity the data are stored on Azure data lake store gen 2 and all the computations will happened on the stored data not from data source. Also able to create linked entity.

D – Apply the ML & AI features to the entities.

E – Can apply incremental refresh on entity level and schedule refresh on dataflow level

F – All the dataflow components are stored on data lake store as a structure.

G – Entities are nothing but tables which can be used on the Power BI desktop using Power BI Dataflow connector and build the dataset and reports.

Microsoft Flow – Find Min or Max Value

This post explains the steps that you need to perform to find the minimum or maximum value from a list of values or a field from a table in Microsoft flow

I am explaining a simple procedure to achieve the result.

Procedure:

1.       Take the first number and store it on a variable

2.       Compare that variable to all the values from the field

3.       Check the Maximum (greater than) value – If else condition

Scenario:

I am taking google sheet which has the column called “Sales”. It has some value. I need to find the maximum value.

Follow the below steps in Microsoft flow

Step 1 – Use any trigger

Step 2 – Action – “Get Rows – Google Sheet” and select the appropriate sheet.

Step 3 – Action – “Select”

From : “Record value”

Map : “Sales” => Sales

Step 4- Action – “Initialize Variable”

Name : “MaximumSales”

Type: “Integer”

Value: int(first(body(‘Select’))?[‘Sales’])

Step 5 – Action – “Apply to each “

Select an output from previous steps: Records value

Condition

i.      int(items(‘Apply_to_each’)?[‘Sales’]) “is greater than” “MaximumSales”

ii.      If Yes

Set Variable

Name: MaximumSales

Value: int(items(‘Apply_to_each’)?[‘Sales’])

Step 6 – Action – “Compose”

Inputs : MaximumSales

That’s it. we got the maximum value. Stay tuned for the use case with this logic.

SAMEPERIODLASTYEAR in SSRS Report builder

This article explains how to use SAMEPERIODLASTYEAR function in SSRS to achieve the same result as like in Power BI.

The challenging part is we don’t have SAMEPERIODLASTYEAR function in SSRS so we need to look for another function which should give the same result.

We have “Previous” function in SSRS, we are going to use that function to achieve the result. Let us get started.

First, let see the result of Power BI – SAMEPERIODLASTYEAR

SAMEPERIODLASTYEAR = CALCULATE(sum(DemoSales[Sales]),

SAMEPERIODLASTYEAR(DemoSales[SalesDate]))

Let us replicate the same result in SSRS.

First, understand the functionality of “Previous” function.

It will give the result of the previous row as the line below.

We need to tune the previous function to get the result as same as SAMEPERIODLASTYEAR in Power BI.

Follow the below steps.

1. Create a Parent Group called Year using “Year” Column
2. Create a Child Group called Month using “Month” Column
3. Create a child group called “Day” using “Day” column.

1. Change the previous function expression as like below.

=Previous(sum(Fields!Sales.Value,”Day”),”Year”)

1. That’s it. We achieve the result as same as Power BI.

Warning:

The result of the previous function will work only on the current data displayed. For example, if you create a parameter for year and filter for the year 2019 then it will not show any value for previous value.