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.