Share a sample implementation for Power BI development and deployment

                                                                                                                Check list of all posts

1. Terms

We define terms as below:

1) Data side: the backend side of the overall Power BI development; the IT department is responsible for extracting, transforming, and loading data into the data warehouse.

2) Report side: the front-end side of the overall Power BI development. The Business Unit is responsible for preparing, modeling from the data warehouse and developing dashboards.

3) Data warehouse developer: responsible for data side development. The objective is to create and populate Tables/Views/Synonyms in Reporting database.

4) Power BI developer: responsible for report side development. The objective is to create and populate Power BI datasets in workspaces, and to author complicated dashboards as needed.

5) Power BI Workspace:  Placeholder for Power BI dataset and Power BI reports. Workspaces support security access controls. Each user(s) will need to grant a workspace(s) to access Power BI.

6) Power BI Dataset: a published data model with data and business logic. Business users can use shared datasets to develop visualizations as needed, while the developer can use them as data sources.

7) Power BI Report authors: develop reports/dashboards based on shared Power BI datasets.

2. Power BI development and deployment overview

The diagram below illustrates the overall Power BI development infrastructure, including Data gateway, Power BI workspaces, and deployment pipeline.

3. Configure data gateway with data-level database security

The objective is only to grant permission to access their tables for each Power BI project. The bottom line is that individual Power BI projects cannot access all tables from the database Reporting. At the same time, we want to control Power BI developers’ permission too.

1) There would be a matching Service Account for every Domain Group, which corresponds to a Power BI project.

2) Service Account gets access to DEV, TEST, and PROD.

3) We are giving the Domain User Group access to all environments incl. DEV, TEST, and PROD

4) Who is responsible for managing access in the Domain User Group? IT solution team, requested from the contact center, open ticket with ServiceNow, and workflow (manager, IT).

5) IT Analytics team is responsible for the Service Account password?

6) We should use these SA to access SharePoint too.

4. Establish Power BI dataset workspaces

Currently, we have designed a single workspace to host the Power BI dataset. Unfortunately, this design doesn't work in practice because of three reasons below:

1) The Power BI deployment pipeline can only deploy datasets from one workspace to another. Therefore, we can't allow the same dataset in the same workspace.

2) We shouldn't allow Power BI developers to modify Production datasets directly. Instead, we should use a deployment pipeline to do the promotion.

3) We must secure the Production Power BI dataset like a database.

Therefore, we have defined consistent workspaces for the Power BI dataset, including DEV, TEST and Production. Each dataset workspace should connect to the database, DEV, TEST, and production, respectively.

5. Enable Power BI developers to efficiently deliver Power BI solutions

The objective is to provide an excellent environment for Power BI developers to develop Power BI solutions effectively.

1) Use PBI Desktop to develop dataset and publish to DEV dataset workspace    

2) Use PBI Service to refresh dataset in DEV dataset workspace  

3) Use PBI Desktop to develop dataset and publish to DEV report workspace

Some of solutions are explored and shared below:

Q1: How is Power BI developer access all databases, incl. DEV, TEST and production?

A1: Power BI developers are assigned to a domain service group based on the database level security design. Therefore, they can access all three databases.

Q2: How to switch database connections for PBI Desktop?

A2: Typically, we are use parameters to switch database connections in a standard way. However, it doesn’t work with the Power BI deployment pipeline. Instead of changing all links for all power queries, you can change SQL server connection with the solution below; once changed, Power BI desktop changes Power queries accordingly.

Q3: How does the Power BI developer refresh the Power BI dataset to connect DEV, TEST, and production?

A3: At the data gateway level, we have added Power BI developers to the data source for DEV, TEST, and production, as Power BI is dataset owner, so they can set up a gateway for Power BI and refresh the dataset.

Q4: How does the Power BI developer refresh the Power BI dataset to connect DEV, TEST, and production?

A4: At the data gateway level, we have added Power BI developers to the data source for DEV, TEST, and production, as Power BI is dataset owner, so they can set up a gateway for Power BI and refresh the dataset.

Q5: How to handle big data volume with Power BI?

A5:  Let the Power BI service refresh dataset instead of using PBI desktop. There are two solutions, use dynamic SQL and Filter data using Power Query. Please note the dynamic SQL doesn't support the Power BI deployment pipeline; therefore, please use the Power query filter and see the appendix for solutions in detail.

6. Promote Power BI datasets to Test and Production with deployment pipelines

As we have decided to use the Power BI deployment pipeline to deploy Power BI dataset, we have figured out two limitations with Power BI development below:

1) It doesn't support dynamic SQL. However, we can use the Power Query filter to resolve this issue, described in the last section

2) It doesn't support parameters with the data source; the solution is provided in the last section.

 

When promoting a dataset, the most helpful feature is to define database connection and parameters rule. The detailed steps are below

step 1: Deploy the dataset to TEST; you need to deploy to enable you to change the rule.

Step 2: Change the connection rule for the dataset. At this moment, the rule is not changed yet.

Step 3: Redeploy the dataset again

step 4: Now you can go gateway to enable connection to TEST database

How to Create Custom Settings in Power BI for Enhanced Filtering

                                                                                                               Check list of all posts

Introduction:

Settings in Power BI allow you to define parameters for filtering data, such as currency and accounts, used in dashboards. While typical settings are implemented on the Power Query side, dataset side, or Excel spreadsheets on SharePoint, they often lack flexibility for end users to modify them. This article presents a practical solution to make settings available within Power BI, enabling users to customize their filtering preferences.

Requirement:

As an example, we'll focus on specifying the currencies and accounts to be used as basic filters for slicers and fact tables like transactions.

Analysis:

To implement these settings effectively, we need to address two challenges: 1) making filtered dimensions, such as currency and account, available on each page where the settings are located, and 2) ensuring that the filtered data influences both slicers and visuals.

Solution:

Although Power BI doesn't offer a direct method to create such solutions, we can employ a workaround. By synchronizing settings across all pages, we can ensure that filtered data is present on each page. Additionally, we can create independent dimensions that serve as filters for slicers in real-time, as well as filter facts using both real slicers and settings.

Implementation:

Step 1: Create two separate dimensions, namely currencyList and AccountList, in the background.

Step 2: Develop a dedicated settings page that incorporates both currencyList and AccountList. It's crucial to synchronize the settings across all pages.

Step 3: Apply filters to each real slicer using the following approach:

Step 4: Establish a connection between the fact table and AccountList. This ensures that when no selection is made in the real slicers, the default AccountList values will be used for all calculations.

By following these steps, you can enable custom settings in Power BI, empowering end users to define their desired filtering options for enhanced data exploration and analysis.

How to Model Monthly DAX Calculations

                                                                                                            Check list of all posts

I(Inspired by insights from DAX Patterns by Alberta and Marco)

The challenge of modeling monthly DAX calculations arises when we are dealing with a non-standardized calendar table. In a typical monthly calendar table, each record represents one Month, resulting in only 12 records for each year. Unfortunately, standard DAX date functions aren’t tailored to work efficiently with this dimension.

Date dimension

Our solution lies in constructing an optimally structured table that easily facilitates the recognition of relative periods. Refer to the Data table below, which elucidates the effectiveness of this approach.

There are three critical columns in this table:

YYYYMM: This unique key serves as an identifier for the date dimension.

YearMonth#: Calculated as the product of the year and 12, plus the month number and minus 1, this magic number enables straightforward period calculations.

YearQuarter#: Another magic number obtained by multiplying the year by 4 , adding the quarter number, and then minus 1.

Introducing these numbers simplifies period calculations significantly. For instance, to retrospect by five months, one would need to subtract 5 from the YearMonth#. If the current Month is 202309, then the YearMonth# is 2023*12 + 9 - 1 = 24284, then 5 months ago should be 24284 - 5 = 24279, which is 202304.

DAX Month date-related functions:

Various scenarios and complex functions might appear daunting; however, the examples outlined here aim to demystify these complexities, offering clear and actionable steps for future undertakings.

YTD: Year-to-Date:

Sales YTD =

VAR LastMonthAvailable =

    MAX ( 'Date'[YearMonth#] )

VAR LastYearAvailable =

    MAX ( 'Date'[Calendar Year] )

VAR Result =

    CALCULATE (

        [Sales Amount],

        REMOVEFILTERS ( 'Date' ),

        'Date'[YearMonth#] <= LastMonthAvailable,

        'Date'[Calendar Year] = LastYearAvailable

    )

RETURN

    Result

QTD: Quarter-to-Date:

Sales QTD =

VAR LastMonthAvailable =

    MAX ( 'Date'[YearMonth#] )

VAR LastYearQuarterAvailable =

    MAX ( 'Date'[YearQuarter#] )

VAR Result =

    CALCULATE (

        [Sales Amount],

        REMOVEFILTERS ( 'Date' ),

        'Date'[YearMonth#] <= LastMonthAvailable,

        'Date'[YearQuarter#] = LastYearQuarterAvailable

    )

RETURN

    Result

MAT: Moving annual total:

Sales MAT =

VAR MonthsInRange = 12

VAR LastMonthRange =

    MAX ( 'Date'[YearMonth#] )

VAR FirstMonthRange =

    LastMonthRange - MonthsInRange + 1

VAR Result =

    CALCULATE (

        [Sales Amount],

        REMOVEFILTERS ( 'Date' ),

        'Date'[YearMonth#] >= FirstMonthRange

            && 'Date'[YearMonth#] <= LastMonthRange

    )

RETURN

    Result

PY: Previous Year:

Sales PY =

VAR CurrentYearNumber = SELECTEDVALUE ( 'Date'[Calendar Year] )

VAR PreviousYearNumber = CurrentYearNumber - 1

VAR Result =

    CALCULATE (

        [Sales Amount],

        REMOVEFILTERS ( 'Date' ),

        'Date'[Calendar Year] = PreviousYearNumber,

        VALUES ( 'Date'[Calendar Month Number] )

    )

RETURN

    Result

PQ: Previous Quarter:

Sales PQ =

VAR CurrentYearQuarterNumber = SELECTEDVALUE ( 'Date'[YearQuarter#] )

VAR PreviousYearQuarterNumber = CurrentYearQuarterNumber - 1

VAR Result =

    CALCULATE (

        [Sales Amount],

        REMOVEFILTERS ( 'Date' ),

        'Date'[YearQuarter#] = PreviousYearQuarterNumber,

        VALUES ( 'Date'[Calendar Month In Quarter Number] )

    )

RETURN

    Result

PM: Previous Month:

Sales PM =

VAR CurrentYearMonthNumber = SELECTEDVALUE ( 'Date'[YearMonth#] )

VAR PreviousYearMonthNumber = CurrentYearMonthNumber - 1

VAR Result =

    CALCULATE (

        [Sales Amount],

        REMOVEFILTERS ( 'Date' ),

        'Date'[YearMonth#] = PreviousYearMonthNumber

    )

RETURN

    Result

PP: Previous Period; automatically selects year, quarter, or month

Sales PP =

SWITCH (

    TRUE,

    ISINSCOPE ( 'Date'[Calendar Year Month] ), [Sales PM],

    ISINSCOPE ( 'Date'[Calendar Year Quarter] ), [Sales PQ],

    ISINSCOPE ( 'Date'[Calendar Year] ), [Sales PY]

)

PYMAT: Previous Moving annual total

Sales PYMAT =

VAR MonthsInRange = 12

VAR LastMonthRange =

    MAX ( 'Date'[YearMonth#] ) - MonthsInRange

VAR FirstMonthRange =

    LastMonthRange - MonthsInRange + 1

VAR Result =

    CALCULATE (

        [Sales Amount],

        REMOVEFILTERS ( 'Date' ),

        'Date'[YearMonth#] >= FirstMonthRange

            && 'Date'[YearMonth#] <= LastMonthRange

    )

RETURN

    Result

YOY: Year Over Year

Sales YOY =

VAR ValueCurrentPeriod = [Sales Amount]

VAR ValuePreviousPeriod = [Sales PY]

VAR Result =

    IF (

        NOT ISBLANK ( ValueCurrentPeriod )

            && NOT ISBLANK ( ValuePreviousPeriod ),

        ValueCurrentPeriod - ValuePreviousPeriod

    )

RETURN

    Result

QOQ: Quarter Over Quarter

Sales QOQ =

VAR ValueCurrentPeriod = [Sales Amount]

VAR ValuePreviousPeriod = [Sales PQ]

VAR Result =

    IF (

        NOT ISBLANK ( ValueCurrentPeriod )

            && NOT ISBLANK ( ValuePreviousPeriod ),

        ValueCurrentPeriod - ValuePreviousPeriod

    )

RETURN

    Result

MOM: Month Over Month

Sales MOM =

VAR ValueCurrentPeriod = [Sales Amount]

VAR ValuePreviousPeriod = [Sales PM]

VAR Result =

    IF (

        NOT ISBLANK ( ValueCurrentPeriod )

            && NOT ISBLANK ( ValuePreviousPeriod ),

        ValueCurrentPeriod - ValuePreviousPeriod

    )

RETURN

    Result

MATG: Moving annual total growth

Sales MATG =

VAR ValueCurrentPeriod = [Sales MAT]

VAR ValuePreviousPeriod = [Sales PYMAT]

VAR Result =

    IF (

        NOT ISBLANK ( ValueCurrentPeriod )

            && NOT ISBLANK ( ValuePreviousPeriod ),

        ValueCurrentPeriod - ValuePreviousPeriod

    )

RETURN

    Result

POP: Period over period; automatically selects year, quarter, or month

Sales POP =

SWITCH (

    TRUE,

    ISINSCOPE ( 'Date'[Calendar Year Month] ), [Sales MOM],

    ISINSCOPE ( 'Date'[Calendar Year Quarter] ), [Sales QOQ],

    ISINSCOPE ( 'Date'[Calendar Year] ), [Sales YOY]

)

PYTD: Previous year to date

Sales PYTD =

VAR LastMonthInYearAvailable =

    MAX ( 'Date'[Calendar Month Number] )

VAR LastYearAvailable = SELECTEDVALUE ( 'Date'[Calendar Year] )

VAR PreviousYearAvailable = LastYearAvailable - 1

VAR Result =

    CALCULATE (

        [Sales Amount],

        REMOVEFILTERS ( 'Date' ),

        'Date'[Calendar Month Number] <= LastMonthInYearAvailable,

        'Date'[Calendar Year] = PreviousYearAvailable

    )

RETURN

    Result

PQTD: Previous quarter to date

Sales PQTD =

VAR LastMonthInQuarterAvailable =

    MAX ( 'Date'[Calendar Month In Quarter Number] )

VAR LastYearQuarterAvailable =

    MAX ( 'Date'[YearQuarter#] )

VAR PreviousYearQuarterAvailable = LastYearQuarterAvailable - 1

VAR Result =

    CALCULATE (

        [Sales Amount],

        REMOVEFILTERS ( 'Date' ),

        'Date'[Calendar Month In Quarter Number] <= LastMonthInQuarterAvailable,

        'Date'[YearQuarter#] = PreviousYearQuarterAvailable

    )

RETURN

    Result

YOYTD: Year over year to date

Sales YOYTD =

VAR ValueCurrentPeriod = [Sales YTD]

VAR ValuePreviousPeriod = [Sales PYTD]

VAR Result =

    IF (

        NOT ISBLANK ( ValueCurrentPeriod )

            && NOT ISBLANK ( ValuePreviousPeriod ),

        ValueCurrentPeriod - ValuePreviousPeriod

    )

RETURN

    Result

QOQTD: Quarter over Quarter to date

Sales QOQTD =

VAR ValueCurrentPeriod = [Sales QTD]

VAR ValuePreviousPeriod = [Sales PQTD]

VAR Result =

    IF (

        NOT ISBLANK ( ValueCurrentPeriod )

            && NOT ISBLANK ( ValuePreviousPeriod ),

        ValueCurrentPeriod - ValuePreviousPeriod

    )

RETURN

    Result

YTDOPY: Year to date Over the previous year

Sales YTDOPY =

VAR ValueCurrentPeriod = [Sales YTD]

VAR ValuePreviousPeriod = [Sales PYC]

VAR Result =

    IF (

        NOT ISBLANK ( ValueCurrentPeriod )

            && NOT ISBLANK ( ValuePreviousPeriod ),

        ValueCurrentPeriod - ValuePreviousPeriod

    )

RETURN

    Result

QTDOPQ: Quarter to date Over the previous Quarter 

Sales QTDOPQ =

VAR ValueCurrentPeriod = [Sales QTD]

VAR ValuePreviousPeriod = [Sales PQC]

VAR Result =

    IF (

        NOT ISBLANK ( ValueCurrentPeriod )

            && NOT ISBLANK ( ValuePreviousPeriod ),

        ValueCurrentPeriod - ValuePreviousPeriod

    )

RETURN

    Result

Create custom matrix visuals in Power BI like discontinuous crosstab in Cognos or tablix in SSRS

                                                                                                               Check list of all posts

Introduction: Achieving a discontinuous crosstab similar to Cognos and the Tablix feature in Paginated Reports has been a sought-after capability in Power BI. This article delves into the process of crafting a customized matrix, mirroring this unique functionality. By closely examining the vertical stitching of tables (http://yinsolutions.blogspot.com/2023/07/how-to-stitch-multiple-power-bi-matrix.html), we unveil an initial solution that addresses this challenge. Furthermore, we explore avenues to extend and adapt this solution to cater to various use cases.

Requirement and Sample Data: We aim to seamlessly stitch two matrix visuals together. With a dataset at hand, we are presented with the task of creating a unified and coherent visualization using a custom matrix. This requirement forms the basis for the solution we will construct.

Solution Overview:

Crafting Custom Columns: To begin, we craft a calculated table that meticulously respects contextual information. This table is designed to ensure that all selected columns adhere to DAX calculations. The calculated table becomes a crucial cornerstone, seamlessly integrating disparate data points.

CustomColumn1 =
VAR _Columns1 =
    SELECTCOLUMNS (
        'DimTab3',
        "ColumnGroup", "Group1",
        "DimKey", DimTab3[Dim3Key],
        "DimName", DimTab3[Dim3Name],
        "SortOrder",
            RANKX (
                ALL ( DimTab3 ),
                DimTab3[Dim3Key],
                ,
                ASC,
                DENSE
            )
    )
VAR _Columns2 =
    SELECTCOLUMNS (
        'DimTab4',
        "ColumnGroup", "Group2",
        "Dim4Key", DimTab4[Dim4Key],
        "Dim4Name", DimTab4[Dim4Name],
        "SortOrder",
            100
                + RANKX (
                    ALL ( DimTab4 ),
                    DimTab4[Dim4Key],
                    ,
                    ASC,
                    DENSE
                )
    )
VAR result =
    UNION (
        _Columns1,
        _Columns2
    )
RETURN
    result

Defining a Generic Matrix Measure: A pivotal element in comprehending this solution lies in defining a generic measure for our matrix. Here, we harness the power of the TREATAS function, enabling dynamic connection and correlation as we craft our visual representation.

MatrixValue =
VAR _Columngroup =
    SELECTEDVALUE ( CustomColumn1[ColumnGroup] )
VAR _Value4Group1 =
    CALCULATE (
        [MeasureX],
        TREATAS (
            VALUES ( 'CustomColumn1'[DimKey] ),
            FactTab1[Dim3Key]
        )
    )
VAR _Value4Group2 =
    CALCULATE (
        [MeasureX],
        TREATAS (
            VALUES ( 'CustomColumn1'[DimKey] ),
            FactTab1[Dim4Key]
        )
    )
VAR result =
    IF (
        _Columngroup = "Group1",
        _Value4Group1,
        _Value4Group2
    )
RETURN
    result

Conclusion: This article has explored the process of designing a custom matrix-like discontinuous crosstab in Power BI. Drawing inspiration from the native capabilities of Cognos and SSRS, we have embarked on a journey of understanding the vertical stitching of tables and translating it into a tailored solution. By constructing a calculated table that upholds context and leveraging the TREATAS function to establish connections, we lay the groundwork for an insightful and adaptable visualization.