Monthly Archives:October 2015

<
ByHariharan Rajendran

PowerBI – Python Support

Microsoft introduced Python support from Power BI on August 2018 update.

PowerBI is supporting R visuals and PowerBI producing custom R visuals too. PowerBI team extended the data science capability of PowerBI by supporting python.

In this post, I am explaining the simple python plot in Power BI.

Download the latest version of Power BI. As python support in preview support, we need to enable the python support preview feature.

 

You should install the Python on your machine so that you can point the python home directory which is mandatory to run any python scripts inside PowerBI.

Once you have configured the setup then you need to test with simple code. Use the below code.

import matplotlib

import numpy as np

import matplotlib.pyplot as plt

x = np.linspace(0, 3*4, 500)

plt.plot(x, np.sin(x**2))

plt.title(‘Test Plot’)

plt.show()

 

The above code works perfectly and produced the plot on jupyter notebook and need to get the same result on PowerBI also.

Drag and drop the python visual into the canvas area. In the below script editor, reference any dummy column and then copy and paste the above script.

Finally, execute the script and it will produce the plot as like below.

 

Now, you can play with your python scripts.

ByHariharan Rajendran

DAX – RelatedTable Scenario

There is a scenario to find the total count of vendor based on the current year and the same customer should exist on the main transaction table.

Let me explain in detail.

Consider, I have a table called vendors which have very simple columns like below.

Vendor ID Vendor Name Created Date
101 V1 Wednesday, February 22, 2017
102 V2 Thursday, December 28, 2017
103 V3 Friday, February 2, 2018
104 v4 Monday, May 7, 2018

 

And Transaction table like below.

Trans ID Vendor ID QTY Sale Amount OrderDate
1 101 5 1500 Tuesday, August 22, 2017
2 101 6 2588 Saturday, December 30, 2017
3 102 8 4500 Monday, April 2, 2018
4 103 12 8000 Thursday, June 7, 2018

 

On the above tables, I want to show the total count of vendors where they are available in the transaction table and their created date should be the current year.

The expected result is “1” as Vendor 103 only available in Transaction table and created date is the current year 2018.

Rest of the rows are not matched with this scenario.

There are various methods are available to achieve the solution. In this post, I am explaining the very simple method.

Step 1: Create a new calculated column on the table Vendors using below script.

IsMatch = IF(Format(TODAY(),”YYYY”) = FORMAT(‘Customer (2)'[Date],”YYYY”), “Match”, “NoMatch”)

It is adding a new column with values like match and nomatch.

 

Step 2: Create another calculated column on the vendor table using below script. We need to use the above-created column.

Count = CALCULATE(IF(ISBLANK(MINX(RELATEDTABLE(‘FACT’),”1″)),0,1),’Customer (2)'[Test]=”Match”)

That’s it. We can use the count column for the result.

 

ByHariharan Rajendran

Import + DirectQuery in Power BI

Microsoft Power BI team has released a new update called “Composite Models” which is in preview now. This post talks about the composite models.
If there is a requirement to use SQL server and Excel for Power BI report, it was possible only with import mode. It means both the data sources should be used as an import option then we can combine the sources and build the report.
What will happen if I want to use SQL Server as a direct query on the above scenario? We couldn’t combine those two data sources together, but it is possible with this composite model.
How to use?
As this is a preview feature, you need to enable this feature on “Preview Features” section.
Once enabled then you can add SQL Server Direct Query and Excel together within Power BI desktop and start building the reports.
Considerations
1. When you decide to go with this feature, you need to ensure to accept the below message. The information of the one data source might have sent to other data source while fetching the data from different sources.

Still, there are few limitations with this composite model.
This composite model consists of below features which we will discuss in our next post.
1. Many-to-Many relationships
2. Storage Mode

ByHariharan Rajendran

Year to Date calculation in DAX

This post explains how to achieve Year to Date calculation using DAX script.

In DAX, there is a pre-defend function available to get the Year to Date calculation. Also, for the quarter to date & month to date.

Those functions are

TOTALYTD()

TOTALQTD()

TOTALMTD()

Instead of using the pre-defend functions, we can use our own defined function like below. To achieve the YTD calculations, use the below functions

  1. CALCULATE
  2. FILTER
  3. ALL

Let us consider a field called “Total Sales”. We will be used the Total Sales & “Date” table.

YTDSales = CALCULATE([Total Sales],

                      FILTER(ALL(‘Date’),

                          ‘Date'[Calendar Year] = MAX(‘Date'[Calendar Year]) &&

                          ‘Date'[Date] <= MAX(‘Date'[Date])))

Compare the result TOTALYTD() with our script.

Explaining the script.

Let us consider that we are in row 2014 – March row on the above table.

YTDSales = CALCULATE([Total Sales],

FILTER(ALL(‘Date’),

‘Date'[Calendar Year] = MAX(‘Date'[Calendar Year]) &&

‘Date'[Date] <= MAX(‘Date'[Date])))

 

‘Date'[Date] <= MAX(‘Date'[Date]) => This line of code returns maximum date. In our case, It will return 31/3/2014

‘Date'[Calendar Year] = MAX(‘Date'[Calendar Year]) => This line of code returns maximum year.  In our case, it will return 2014

ALL(‘Date’) => This line of code returns all the dates from date table.

FILTER(ALL(‘Date’),

‘Date'[Calendar Year] = MAX(‘Date'[Calendar Year]) &&

‘Date'[Date] <= MAX(‘Date'[Date])))

The above line of codes returns, current row year and last date of the current row month.

 

ByHariharan Rajendran

Connect Live option disabled

This post solves the very simple problem in Power BI.

If you are trying to access the Power BI datasets to create reports then you may encounter this below error message.

Error: “The connect live option for this file is disabled because it already contains data from another data source.”

“Power BI datasets” is one of the options from “Get Data”.  Whenever you publish a report from Power BI desktop to Power BI services, it is publishing the dataset and report.

You can create multiple reports using the same published datasets from Power BI services. This is something like a shared dataset. In case, if you are experiencing the above error then you need to ask your administrators to fix the issues.

The administrator has to fix this issue on the admin side of the Power BI services. Follow the below steps.

  1. Go Power BI cloud services
  2. Go to the Admin portal
  3. Click Tenant settings on the left side and check the export and sharing settings
  4. Enable the export data feature.

That’s it. The issue has been fixed.

ByHariharan Rajendran

Anybody can use R visuals in Power BI

This post explains that anyone can get R visuals for their data with ggplot2.

Power BI is very famous for different types of visualizations right from out of the box visualizations, R visualizations and custom visualizations.

To generate R visuals, you need to write R codes on the editor window. It is possible when you have a basic knowledge of R packages. Microsoft makes this process easy and provided many R custom visuals which is like other visuals where you can drag and drop the fields on the respective axis.

R custom visuals doesn’t require R codes.

Another option that you can easily create R visuals using ggplot2 builder without writing much code.

Esquisse is a small add-in to R studio helps you to interactively explore your data by visualizing it with the ggplot2 package. It allows you to draw bar graphs, curves, scatter plots, histograms, then export the graph or retrieves the code generating the graph.

Refer the below URL,

https://github.com/dreamRs/esquisse

Follow the below steps to use this on your Power BI Desktop.

You need to have R studio installed on your machine

Install the devtools using below command.

install.packages(“devtools”)

Install the “dreamRs/esquisse” using below line of code

devtools::install_github(“dreamRs/esquisse”)

install.packages(“ggplot2”)

You need to perform the above steps in the R studio.

Once above steps are performed then you are good to go with Power BI Desktop.

You can take any dataset which you want to use it for R visuals. Try to have different columns like date, number string to experience all the visuals from ggplot2.

Add the R visual into the canvas area and it will open the editor below. To add a R script, you need to choose the fields from your dataset.

When you added the fields, you can see the R editor enabled to write codes.

Copy and paste the below line of code and click the run button.

esquisse:::esquisser()

It will open your browser and load the ggplot2 builder.

It will look like below.

First, you need to choose the dataset from the drop down of the data.frame list.

It will show all the columns as variables. You need to select all or whichever you want to use it for your visuals. Then finally choose the selected variables will be added.

On the Left top, you can see “auto”, you can click and see what are the visuals are supported as of now.

Next, you need to drag and drop the variables on respective sections. The sections are,

X Axis

Y Axis

Fill

Color

Size

Based on the data type you can choose different visuals. Sometimes few visual will not be enabled, if that is the case then your data is supported for those visuals.

To customize the visual, you can use the bottom bar options.

Label & Title

Plot Options

Data – Filter

Once you satisfied with the visual then you can copy the generated code and paste it on the R editor in Power Bi Desktop.

Before running the script, you need to comment on the previously added line of script and add the below line of code.

Library (ggplot2)

Then run the script.

It will generate the R visual on your report.

Thanks. Let me know if you have any doubts. Reach me on hari@yoursqlman.com.

ByHariharan Rajendran

DAX – RELATED & RELATEDTABLE

This post explains the concept of below DAX functions

  1. RELATED
  2. RELATEDTABLE

I need to explain the data modelling little bit before going to DAX functions. As you know, we need to create a relationship either manually or automatically to use the tables together to get the expected result.

We have 3 different relationships available.

  1. One-to-One Relationship
  2. One-to-Many Relationship
  3. Many-to-Many Relationship

In the context, let us consider a one-to-many relationship. If you have CUSTOMER master and FACT tables then trying to create a relationship, then that would create a one-to-many relationship.

It means we have unique records on the customer table but multiple same values on the FACT table with reference to the connected columns.

If you want to create a new calculated column on the CUSTOMER table then you need to use RELATEDTABLE DAX function. On the other hand, when you want to create a calculated column on the FACT table then you need to use the RELATED function.

RELATEDTABLE:

Customer First Purchase = MINX(
RELATEDTABLE(‘Sale’),
‘Sale'[Invoice Date Key])

 

RELATED:

State = RELATED(‘City'[State Province])

ByHariharan Rajendran

[Solved] Microsoft Access: The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine

This post helps you solve the below issue in Power BI.

Scenario: There will be an error when we connect with 32-bit Access Database from Power BI 64-bit desktop.

Error: “Microsoft Access: The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine”.

Solution

To solve the above issue, follow the below steps.

  1. Visit https://www.microsoft.com/en-gb/download/details.aspx?id=13255
  2. Download 64-bit version of the software.
  3. Install on the machine where Power BI is installed.

Once completed the above steps, try to connect access DB from Power BI desktop. This time you will be able to connect without an issue.

Post your comments if you get any more issues in this scenario.