Sunday, August 26, 2018

How to use DATESBETWEEN Function in Power BI DAX

DATESBETWEEN Function (DAX)
The is Function returns a table that contains a column of dates that begins with the start_date and continues until the end_date.

Syntax :
DATESBETWEEN(<dates>,<start_date>,<end_date>) 

dates - is reference to a date/time column.
start_date - is a date expression.
end_date - is a date expression.

Remarks
If start_date is a blank date value, then start_date will be the earliest value in the dates column.
If end_date is a blank date value, then end_date will be the latest value in the dates column.
The dates used as the start_date and end_date are inclusive: that is, if the sales occurred on September 1 and you use September 1 as the start date, sales on September 1 are counted.

Example :
The following sample DAX formula creates a measure that calculates the 'Q4FY16 Sales' from the FactSales table.
Q4FY16_Sales = 
CALCULATE(SUM(FactSales[Net_Sales]), DATESBETWEEN(tbl_Calendar[Date_Id],  
    DATE(2016,10,1),  
    DATE(2016,12,31)  
  ))

Note:
The DATESBETWEEN function is provided for working with custom date ranges. If you are working with common date intervals such as months, quarters, and years, we recommend that you use the appropriate function, such as DATESINPERIOD.

-------------------------------------------------------------------------------------------------------- 
Thanks, TAMATAM ; Business Intelligence & Analytics Professional 
--------------------------------------------------------------------------------------------------------

How to use CALENDAR and CALENDARAUTO Functions in Power BI DAX

What is the difference between CALENDAR and CALENDARAUTO Functions in Power BI DAX
CALENDAR Function (DAX)
This function returns a table with a single column named “Date” that contains a contiguous set of dates. The range of dates is from the specified start date to the specified end date, inclusive of those two dates.

Syntax :
CALENDAR(<start_date>, <end_date>)
start_date - is any DAX expression that returns a datetime value.
end_date is any DAX expression that returns a datetime value.

Remarks
An error is returned if start_date is greater than end_date.

Example :
The following formula returns a table with dates between January 1st, 2015 and December 31st, 2020.

=CALENDAR (DATE (2015, 1, 1), DATE (2020, 12, 31))
For a data model which includes actual Sales data and future sales Forecast. The following expression returns the date table covering the range of dates in these two tables.
=CALENDAR (MINX (Sales, [Date]), MAXX (Forecast, [Date]))


CALENDARAUTO Function (DAX) :
This function returns a table with a single column named “Date” that contains a contiguous set of dates. The range of dates is calculated automatically based on data in the model.
Syntax :
CALENDARAUTO([fiscal_year_end_month])

fiscal_year_end_month- is any DAX expression that returns an integer from 1 to 12. If omitted, defaults to the value specified in the calendar table template for the current user, if present; otherwise, defaults to 12.

Remarks:
The date range is calculated as follows:
  • The earliest date in the model which is not in a calculated column or calculated table is taken as the MinDate.
  • The latest date in the model which is not in a calculated column or calculated table is taken as the MaxDate.
  • The date range returned is dates between the beginning of the fiscal year associated with MinDate and the end of the fiscal year associated with MaxDate.
  • An error is returned if the model does not contain any datetime values which are not in calculated columns or calculated tables.
Example :
In this example, the MinDate and MaxDate in the data model are July 1, 2010 and June 30, 2015.

CALENDARAUTO() will return all dates between January 1, 2010 and December 31, 2015.
CALENDARAUTO(3) will return all dates between March 1, 2010 and February 28, 2016.

-------------------------------------------------------------------------------------------------------- 
Thanks, TAMATAM ; Business Intelligence & Analytics Professional 
--------------------------------------------------------------------------------------------------------

What are On-Premises data gateways in Power BI

What are Power BI Gateways
A Power BI gateway is software that you install within an on-premises network; it facilitates access to data in that network. It's like a gatekeeper that listens for connection requests, and grants them only when a users' requests meet certain criteria. This lets organizations keep databases and other data sources on their on-premises networks, yet securely use that on-premises data in Power BI reports and dashboards.

A gateway can be used for a single data source or multiple data sources. The following diagram shows a basic view, with the gateway handling requests from the cloud for three on-premises computers. We'll expand on this later in the article.

The On-premises data gateway acts as a bridge, providing quick and secure data transfer between on-premises data (data that is not in the cloud) and the Power BI, Microsoft Flow, Logic Apps, and PowerApps services.


You can use a single gateway with different services at the same time. If you are using Power BI as well as PowerApps, a single gateway can be used for both. It is dependent on the account you sign in with.

Types of gateways
Power BI offers two gateways, each for a different scenario:

On-premises data gateway (Personal mode) – allows one user to connect to sources, and can’t be shared with others. Can only be used with Power BI. This gateway is well-suited to scenarios where you’re the only person who creates reports, and you don't need to share the data sources with others.

On-premises data gateway(Enterprise mode) – allows multiple users to connect to multiple on-premises data sources. Can be used by Power BI, PowerApps, Flow, Azure Analysis Services, and Azure Logic apps, all with a single gateway installation. This gateway is well-suited to more complex scenarios with multiple people accessing multiple data sources.


Using a gateway
There are four main steps for using a gateway:

  1. Install the gateway on a local computer, using the appropriate mode.
  2. Add users to the gateway, so they can access on-premises data sources.
  3. Connect to data sources, so they can be used in reports and dashboards.
  4. Refresh on-premises data, so Power BI reports are up to date.
You can install a stand-alone gateway or add a gateway to a cluster, which is recommended for high availability.

How gateways work
The gateway you install runs as a Windows service, On-premises data gateway. This local service is registered with the Gateway Cloud Service through Azure Service Bus. The following diagram shows the flow between on-premises data and the cloud services that use the gateway.


Queries and data flow:

  1. A query is created by the cloud service with the encrypted credentials for the on-premises data source. It's then sent to a queue for the gateway to process.
  2. The gateway cloud service analyzes the query and pushes the request to the Azure Service Bus.
  3. The on-premises data gateway polls the Azure Service Bus for pending requests.
  4. The gateway gets the query, decrypts the credentials, and connects to the data sources with those credentials.
  5. The gateway sends the query to the data source for execution.
  6. The results are sent from the data source, back to the gateway, and then onto the cloud service and your server.
Requirements :
The following are the basic requirements to install a On-Premises Gateway.
Minimum Requirements:
  1. .NET 4.6 Framework
  2. 64-bit version of Windows 7 / Windows Server 2008 R2 (or later)
Recommended:
  1. 8 Core CPU
  2. 8 GB Memory
  3. 64-bit version of Windows 2012 R2 (or later)
Related Considerations:
  1. The gateway cannot be installed on a domain controller
  2. If you are planning to use Windows authentication, make sure you install the gateway on a computer that is a member of the same Active Directory environment as the data source(s).
  3. You shouldn't install a gateway on a computer, such a laptop, that may be turned off, asleep, or not connected to the Internet because the gateway can't run under any of those circumstances. In addition, gateway performance might suffer over a wireless network.
  4. Analysis Services is not required to use the gateway. You can use the gateway to connect to an Analysis Services data source.

Limitations of Analysis Services live connections:
You can use a live connection against tabular or multidimensional instances.
Server versionRequired SKU
2012 SP1 CU4 or laterBusiness Intelligence and Enterprise SKU
2014Business Intelligence and Enterprise SKU
2016Standard SKU or higher
  • Cell level Formatting and translation features are not supported.
  • Actions and Named Sets are not exposed to Power BI, but you can still connect to multidimensional cubes that also contain Actions or Named sets and create visuals and reports.
List of available data source types :
Data sourceLive/DirectQueryUser configured manual 
or scheduled refresh
Analysis Services TabularYesYes
Analysis Services MultidimensionalYesYes
FileNoYes
FolderNoYes
IBM DB2NoYes
IBM Informix DatabaseNoYes
IBM NetezzaYesYes
ImpalaYesYes
MySQLNoYes
ODataNoYes
ODBCNoYes
OledbNoYes
OracleYesYes
PostgresSQLNoYes
SAP BWYesYes
SAP HANAYesYes
SharePoint list (on-premises)NoYes
SnowflakeYesYes
SQL ServerYesYes
SybaseNoYes
TeradataYesYes
WebNoYes


Download and install the On-premises data gateway :
To download the gateway, select Data Gateway under the Downloads menu. Download the On-premises data gateway.

Note that updating the On-premises data gateway is achieved by reinstalling the gateway, as described in this section. When updating the gateway (by reinstalling), your existing gateways settings are retained.


Install the On-premises data gateway


  • On-premises data gateway(Enterprise Mode): Multiple users can share and reuse a gateway in this mode. This gateway can be used by Power BI, PowerApps, Flow or Logic Apps. For Power BI, this includes support for both schedule refresh and DirectQuery
  • On-premises data gateway(Personal Mode): This is for Power BI only and can be used as an individual without any administrator configuration. This can only be used for on-demand refresh and schedule refresh. This selection launchs installation of the personal gateway.



There are a few things to note about installing either mode of the gateway:
  • both gateways require 64-bit Windows operating systems
  • gateways can’t be installed on a domain controller
  • you can install up to two On-premises data gateways on the same computer, one running in each mode (personal and standard).
  • you cannot have more than one gateway running in the same mode on the same computer.
  • you can install multiple On-premises data gateways on different computers, and manage them all from the same Power BI gateway management interface (excluding personal, see the following bullet point)
  • You can only have one Personal mode gateway running for each Power BI user. If you install another Personal mode gateway for the same user, even on a different computer, the most recent installation replaces the existing previous installation.
The data gateway installs and runs on your computer. It is best to install the gateway on a machine that can be left running all the time.

Here are a few things to consider before installing the gateway.
  • If you are installing on a laptop, and your laptop is turned off, not connected to the internet, or asleep the gateway won’t work and the data in the cloud service will not be synchronized with your on-premises data.
  • If your machine is connected to a wireless network, the gateway may perform more slowly which will cause it to take longer to synchronize the data in the cloud service with your on-premises data.
Once the gateway is installed, you will need to sign in with your work or school account.



After you are signed in, you will have the option to configure a new gateway, or to migrate, restore, or take over an existing gateway.



Configure a new gateway

  1. Enter a name for the gateway
  2. Enter a recovery key. This has to be a minimum of 8 characters.
  3. Select Configure.


Note:
The recovery key will be needed if you ever need to migrate, restore or take over a gateway. Be sure to keep this key in a safe place.

Migrate, restore or take over an existing gateway
You will need to select the gateway you want to recover and supply the recovery key that was used to first create the gateway.


On-premises data gateway connected
Once the gateway is configured, you will be able to make use of it to connect to on-premises data sources.

If the gateway is for Power BI, you will need to add your data sources to the gateway within the Power BI service. This is done within the Manage gateways area. You can refer to the manage data sources articles for more information.

For PowerApps, you will need to select a gateway for a defined connection for supported data sources. For Flow and Logic Apps, this gateway is ready to be used with your on-premises connections.


Install the gateway in personal mode :
The Personal version of gateway only works with Power BI.
After the personal gateway is installed, you will need to launch the Power BI Gateway - Personal Configuration Wizard.

You will then need to sign into Power BI to register the gateway with the cloud service.

You will also need to supply the windows user name and password that the windows service will run as. You can specify a different Windows account from your own. The gateway service will run using this account.
After the installation is complete, you will need to go to your datasets within Power BI and make sure credentials are entered for your on-premises data sources.

Source : 
https://docs.microsoft.com/en-us/power-bi/service-gateway-onprem

-------------------------------------------------------------------------------------------------------- 
Thanks, TAMATAM ; Business Intelligence & Analytics Professional 
--------------------------------------------------------------------------------------------------------

Friday, August 17, 2018

How to use ALL and ALLEXCEPT Functions in Power BI DAX

ALL () Function in Power BI DAX
The ALL() Function returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied. This function is useful for clearing filters and creating calculations on all the rows in a table.
This function is not used by itself, but serves as an intermediate function that can be used to change the set of results over which some other calculation is performed.
Syntax :
ALL( {<table> | <column>[, <column>[, <column>[,…]]]} )

--<table> : The table that you want to clear filters on.
--<column> : The column that you want to clear filters on.
The argument to the ALL function must be either a reference to a base table or a reference to a base column. You cannot use table expressions or column expressions with the ALL function.
ALL(Table) :
Removes all filters from the specified table. In effect, ALL(Table) returns all of the values in the table, removing any filters from the context that otherwise might have been applied.
This function is useful when you are working with many levels of grouping, and want to create a calculation that creates a ratio of an aggregated value to the total value.
ALL (Column[, Column[, …]]) :
Removes all filters from the specified columns in the table; all other filters on other columns in the table still apply. All column arguments must come from the same table.
The ALL(Column) variant is useful when you want to remove the context filters for one or more specific columns and to keep all other context filters.
Example-I :
Suppose we have the Sales data by Region as follows..
Now, we apply a Filter in Region, excluded "Africa" from the Region_Name column.



Now, If you wants to ignore that Filter and wants to calculate Sum for All regions, we can calculate using ALL () with SUM() Functions as follows..
All_Region_Sales = 
CALCULATE(
                       SUM(FactSales[Net_Sales]),
                       ALL(DimRegion[Region_Name])
                      )
Here, the ALL() Function clears/ignore the filters on column [Region_Name]. 
Result :
Example-II :
Now we will calculate the Sales Penetration by Country using the SUMX ( ) , CALCULATE() and ALL() Functions as follows..

Sales_Penetration_by_Country = 
                                                SUMX(FactSales,FactSales[Net_Sales])/
                                                CALCULATE(SUM(FactSales[Net_Sales]),ALL(DimRegion))

Result :

ALLEXCEPT () Function in Power BI DAX
Removes all context filters in the table except filters that have been applied to the specified columns.

Syntax :
ALLEXCEPT( {<table> | <column>[, <column>[, <column>[,…]]]} )
--<table> : The table that you want to clear filters on.
--<column> : The column that you want to clear filters on.
The first argument to the ALLEXCEPT function must be a reference to a base table; all subsequent arguments must be references to base columns. You cannot use table expressions or column expressions with the ALLEXCEPT function.
ALLEXCEPT(Table, Column1 [,Column2]...):
Removes all context filters in the table except filters that are applied to the specified columns.
This is a convenient shortcut for situations in which you want to remove the filters on many, but not all, columns in a table.

Example :
We will calculate the SUM by ignoring the Filters on other Columns of the DimRegion table except on DimRegion[Region_Name] column, using the ALLEXCEPT Function as follows..
AllExcept_Region_Sales = 
                                    CALCULATE(                      
                                                      SUM(FactSales[Net_Sales]),
                                                      ALLEXCEPT(DimRegion,DimRegion[Region_Name])
                                                      )                     
Result :

------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

Thursday, August 16, 2018

How to use FILTER Function in Power BI DAX

FILTER Function in Power BI DAX
The FILTER Function is used to reduce the number of rows in the table that you are working with, and use only specific data in calculations. FILTER is not used independently, but as a function that is embedded in other functions that require a table as an argument.

Syntax :
FILTER(<table>,<filter>) 
-- <Table> : The table to be filtered. The table can also be an expression that results in a table.
-- <Filter> : A Boolean expression that is to be evaluated for each row of the table. 
For example, [Amount] > 0 or [Region] = "India"

Example :
Suppose we have data model as follows..


The Sales by Region , Country and Customer Segment as follows.

Calculating the Sum of Sales for ASIA and EUROPE Regions using FILTER Function as follows..

Sales_Asia_Europe = 
CALCULATE(                                             
                     SUM(FactSales[Net_Sales]),
                     FILTER(DimRegion,OR([Region_Name]="Asia" ,[Region_Name]="Europe"))
                    )
Result :

Calculating Sum of Sales for ASIA and EUROPE Regions and the Countries "India" and "France" using the Logical operators with FILTER Function as follows..

Sales_India_France = 
CALCULATE(
                SUM(FactSales[Net_Sales]),
FILTER(DimRegion,([Region_Name]="Asia"||[Region_Name]="Europe")
                            && ([Country]="India" ||[Country]="France"))
                    )

Calculating Sum of Sales for ASIA Region and the Customer Segment "Electronics" using the multiple FILTER Functions as follows..

Electro_Sales_Asia = 
CALCULATE(
                      SUM(FactSales[Net_Sales]),
      FILTER(DimRegion,[Region_Name]="Asia"),
                      FILTER(DimCustomers,[Cust_Segment]="Electronics" )
                     )

Result :

Calculating Sum of Sales for specific Regions using RELATED Function with FILTER Functions as follows..here RELATED Function performs the lookup for the specified Region_Ids against the entire FactSales table and then SUM Function aggregates the corresponding values.

Lookup_Asia_Sales = 
CALCULATE(
                       SUM(FactSales[Net_Sales]),
                       FILTER(FactSales,
                           OR(RELATED(DimRegion[Region_Id])=98769,
                                  RELATED(DimRegion[Region_Id])=98770))
                 )

Result :

If we wants to show/repeat Asia_Sales for each regions, we need use ALL Function on the FactSales Table as shown below..

Lookup_Asia_Sales = 
CALCULATE(
                       SUM(FactSales[Net_Sales]),                                                                                                     FILTER(All(FactSales),OR(
                       RELATED(DimRegion[Region_Id])=98769,
                       RELATED(DimRegion[Region_Id])=98770))
                    )

Result :

-------------------------------------------------------------------------------------------------------- 
Thanks, TAMATAM ; Business Intelligence & Analytics Professional 
--------------------------------------------------------------------------------------------------------

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog