Scenario:
Suppose, we wants to load the Data from an Azure Analysis Services (SSAS) Cube / Tabular Model Database, by passing the Parameters like "LoadYear" (Eg: >=2015) . Also, we want to pass a another Parameter like "RunFlag" to enable(1) or disable(0) data load from DAX Query result.
We can achieve this Scenario, by defining and passing the Power BI Parameters in to the DAX Query as follows:
1) First define the required Power BI Parameters for DataSource, Dataset, LoadYear, RunFlag
Datasource : It is a required Parameter of the type Text, that holds the ServerName of a Data Source ( e.g: localhost:51072 )
Dataset : It is a required Parameter of the type Text, that holds a name of the source Dataset.
( e.g: b5e52c96-862a-4f7a-a500-05c8653a84b7 )
LoadFlag : It is a required Parameter of the type Number, that holds the values as 1 or 0. This is the most important Parameter, which helps to enable load(when 1) or disable load(when 0).
LoadYear : It is a required Parameter of the type Number, that holds the values as 2014,2015. This is also the most important Parameter, which helps to limit the data load by a Year, if your dataset contains a Year field.
2) Next pass these Power BI Parameters for in to the DAX Query as shown below example:
VAR vRunFlag = 1
--VAR vYear_Filter>=LoadYear(Eg:2015)
VAR vCntry_Filter = { "United States", "Canada","France" }
VAR vAddCols1 =
FILTER (
ADDCOLUMNS (
tbl_Sales,
"FY_Year", RELATED ( tbl_Calendar[Year] ),
"QTR_Year", RELATED ( tbl_Calendar[QTR_Year] ),
"Cntry_Name", RELATED ( tbl_Country[Country] ),
"Prod_Name", RELATED ( tbl_Products[Prod_Name] )
),
[Cntry_Name] IN vCntry_Filter &&
--VAR vYear_Filter>=LoadYear(Eg:2015)
VAR vCntry_Filter = { "United States", "Canada","France" }
VAR vAddCols1 =
FILTER (
ADDCOLUMNS (
tbl_Sales,
"FY_Year", RELATED ( tbl_Calendar[Year] ),
"QTR_Year", RELATED ( tbl_Calendar[QTR_Year] ),
"Cntry_Name", RELATED ( tbl_Country[Country] ),
"Prod_Name", RELATED ( tbl_Products[Prod_Name] )
),
[Cntry_Name] IN vCntry_Filter &&
/* LoadYear is PBI Parameter to Limit Data load by Year*/
[FY_Year] >= Number.ToText(LoadYear)
)
VAR vSummary1 =
SUMMARIZE (
vAddCols1,
[FY_Year],
[QTR_Year],
[Cntry_Name],
[Prod_Name],
"Total_Sales", SUM ( tbl_Sales[Gross_Sales] ),
"Discount", SUM ( tbl_Sales[Gross_Sales] ) * 0.05
)
EVALUATE
FILTER (
SELECTCOLUMNS (
vSummary1,
"FY_Year", [FY_Year],
"FY_QTR", [QTR_Year],
"Counntry", [Cntry_Name],
"Prod_Name", [Prod_Name],
"NetSales",
( [Total_Sales] - [Discount] )
),
/* LoadFlag is PBI Parameter. 1 enables the Dataload and 0 disables. */
vRunFlag = Number.ToText(LoadFlag)
)
)
VAR vSummary1 =
SUMMARIZE (
vAddCols1,
[FY_Year],
[QTR_Year],
[Cntry_Name],
[Prod_Name],
"Total_Sales", SUM ( tbl_Sales[Gross_Sales] ),
"Discount", SUM ( tbl_Sales[Gross_Sales] ) * 0.05
)
EVALUATE
FILTER (
SELECTCOLUMNS (
vSummary1,
"FY_Year", [FY_Year],
"FY_QTR", [QTR_Year],
"Counntry", [Cntry_Name],
"Prod_Name", [Prod_Name],
"NetSales",
( [Total_Sales] - [Discount] )
),
/* LoadFlag is PBI Parameter. 1 enables the Dataload and 0 disables. */
vRunFlag = Number.ToText(LoadFlag)
)
3) Finally, we need to modify the Power Query in a way, that it should read the values of the Parameter passed in the DAX Query.
The below one is the Final version of the DAX Query from the Power Query, where we have Passed the parameters with syntax as : & Parameter & , and adjusted the double quotes.
AnalysisServices.Database(DataSource, Dataset,
[Query=" DEFINE
VAR vRunFlag = 1
--VAR vYear_Filter=LoadYear
VAR vCntry_Filter = { ""United States"", ""Canada"",""France"" }
VAR vAddCols1 =
FILTER (
ADDCOLUMNS (
tbl_Sales,
""FY_Year"", RELATED ( tbl_Calendar[Year] ),
""QTR_Year"", RELATED ( tbl_Calendar[QTR_Year] ),
""Cntry_Name"", RELATED ( tbl_Country[Country] ),
""Prod_Name"", RELATED ( tbl_Products[Prod_Name] )
),
[Cntry_Name] IN vCntry_Filter &&
/* LoadYear is PBI Parameter to Limit Data load by Year*/
[FY_Year] >=" & Number.ToText(LoadYear) &
")
VAR vSummary1 =
SUMMARIZE (
vAddCols1,
[FY_Year],
[QTR_Year],
[Cntry_Name],
[Prod_Name],
""Total_Sales"", SUM ( tbl_Sales[Gross_Sales] ),
""Discount"", SUM ( tbl_Sales[Gross_Sales] ) * 0.05
)
EVALUATE
FILTER (
SELECTCOLUMNS (
vSummary1,
""FY_Year"", [FY_Year],
""FY_QTR"", [QTR_Year],
""Counntry"", [Cntry_Name],
""Prod_Name"", [Prod_Name],
""NetSales"",( [Total_Sales] - [Discount] )
),
/* LoadFlag is PBI Parameter. 1 enables the Dataload and 0 disables. */
vRunFlag =" & Number.ToText(LoadFlag) &
")",
Implementation="2.0"])
Result:
Now lets change the LoadFlag from 1 to 0 and keep the LoadYear flag as is, and see the result. Now the query will not return any data as LoadFlag=0
We can manage these Parameters in Power BI Service, to enable the Load, once we Publish the Report to the Service.
This is the most useful and Powerful technique helps in managing the Data Loads during the phase of the Report Development.
--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------
No comments:
Post a Comment
Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.