Friday, April 29, 2022

How to Limit Data Load from SSAS Database using Parameters in DAX Query in Power BI

How to pass Power BI Parameters in DAX Query to Limit Data Load from SSAS Database in to Power BI
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:


DEFINE
    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 && 
                /* 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) 
)

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


Result:


Notes :
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.

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