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

--------------------------------------------------------------------------------------------------------