Can we have dynamic columns in a matrix in Power BI

[divya819]

Hi,

I have to create one matrix in power bi like below 

We have Orders booked in some time. we have to show how they are shipping. It is also possible order is not shipped completely.

Problem is If I insert Order Booked in value section and shipped quarters in column then Order Booked column is coming under Shipped Quarters and distributing for all shipped quarters.

I want a single Order Booked column and 1 measure so when i drag that measure it got split into required no. of columns

Input Data

Any help !! 

[DataNinja777]

Hi @divya819 ,

One way to keep the matrix manageable while still using a single measure and a disconnected table is to dynamically generate only the quarters you actually want. For instance, you could create a “ColumnsForMatrix” table that includes “Order Booked” plus just a limited window (for example, the past three quarters and the next three quarters). First, ensure you have a separate Calendar table with a field like [QuarterKey] (a numeric or date-based key) and a [QuarterLabel] (such as “2024 Q1”). Then create the following table in DAX:

ColumnsForMatrix =
VAR TodayQtrKey =
    CALCULATE(
        MAX('Calendar'[QuarterKey]),
        'Calendar'[Date] = TODAY()
    )
VAR StartQtr = TodayQtrKey - 3
VAR EndQtr   = TodayQtrKey + 3
RETURN
UNION(
    DATATABLE(
        "ColumnLabel",
        STRING,
        {
            { "Order Booked" }
        }
    ),
    SELECTCOLUMNS(
        FILTER(
            ALL('Calendar'[QuarterKey], 'Calendar'[QuarterLabel]),
            'Calendar'[QuarterKey] >= StartQtr &&
            'Calendar'[QuarterKey] <= EndQtr
        ),
        "ColumnLabel",
        'Calendar'[QuarterLabel]
    )
)

This code identifies the current quarter key (TodayQtrKey), then only returns the rows from the Calendar table whose keys fall within a window of three quarters before and three quarters after. It also unions a row labeled “Order Booked” at the top. Next, define a measure that checks which column label is currently in context: if it’s “Order Booked,” it will sum all amounts (ignoring shipped date), otherwise it will sum only those rows whose shipped quarter matches the column label. The measure might look like this:

Dynamic Matrix Measure =
VAR SelectedColumn = SELECTEDVALUE(ColumnsForMatrix[ColumnLabel])
RETURN
    IF(
        SelectedColumn = "Order Booked",
        CALCULATE(
            SUM('FactTable'[Amount]),
            ALL('FactTable'[Order Shipped date])
        ),
        CALCULATE(
            SUM('FactTable'[Amount]),
            TREATAS( { SelectedColumn }, 'Calendar'[QuarterLabel] )
        )
    )

In your matrix, place [Order Creation Date] (from your FactTable or a related Date dimension) on Rows, place ColumnsForMatrix[ColumnLabel] on Columns, and place Dynamic Matrix Measure in Values. Now you get one column for “Order Booked” plus only a handful of quarters around the current quarter, and as time progresses, those columns automatically shift to reflect the current period window. This approach remains scalable because you decide how many quarters to include, and you still get the benefits of having a single measure that adapts based on the column context.

Best regards,

[danextian]

Hi @divya819 

Use a separate date dimensions table. Create  active an inactive relationships. Invoke the inactive relationship in a measure using USERELATIONSHIP.

The broken lines in the image below are for inactive relationships.

These are the measures used in the image below them which quarter column is from the dates table in the screenshot above.

Count of Orders = 
COUNTROWS ( Orders )

Orders by Shipment Date = 
CALCULATE (
    [Count of Orders],
    USERELATIONSHIP ( Dates[Date], Orders[Shipment Date] )
)


Orders by Receipt Date = 
CALCULATE (
    [Count of Orders],
    USERELATIONSHIP ( Dates[Date], Orders[Order Receipt Date] )
)

Please see the attached sample pbix.

[divya819]

Thanks a lot !

But I want output like this only 

[v-bmanikante]

Hi @divya819 ,

Thank you for reaching out to Microsoft Fabric Community forum.

@DataNinja777 @danextian Thank you for your responses.

If your issue is still not resolved, please try the following steps:

• Create a Calculated Table using this DAX :

ColumnType1 =

DATATABLE (

    "ColumnType", STRING,

    "Number", INTEGER,

    {

        { "Order Booked", 1 },

        { "2024 Q1 Shipped", 2 },

        { "2024 Q2 Shipped", 3 },

        { "2024 Q3 Shipped", 4 },

        { "2024 Q4 Shipped", 5 },

        { "2025 Q1 Shipped", 6 }

    }

)

No relationship is needed between this new ColumnType1 table and your Orders table.

After creating, make sure to sort the ColumnType column using number column (number-based) to maintain the correct sequence in the matrix.

• Create a Measure like below:

Order Matrix Value =
VAR SelectedColumn = SELECTEDVALUE(ColumnType1[ColumnType])
VAR OrderCreation = SELECTEDVALUE(Orders[Order Creation Date])
RETURN
SWITCH(
    TRUE(),
    SelectedColumn = "Order Booked",
        CALCULATE(
            SUM(Orders[Amount]),
            Orders[Order Creation Date] = OrderCreation
        ),
    CONTAINSSTRING(SelectedColumn, "Shipped"),
        VAR ShipQuarter = SUBSTITUTE(SelectedColumn, " Shipped", "")
        RETURN
            CALCULATE(
                SUM(Orders[Amount]),
                Orders[Order Creation Date] = OrderCreation,
                Orders[Order Shipped date] = ShipQuarter
            ),
    BLANK()
)

I have also attached the PBIX file for your reference.

Please check and let me know if you still face any issues!

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

Regards,

B Manikanteswara Reddy

[v-bmanikante]

Hi @divya819 ,

We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

B Manikanteswara Reddy

[v-bmanikante]

Hi @divya819 ,

We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

B Manikanteswara Reddy

[v-bmanikante]

Hi @divya819 ,

We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

B Manikanteswara Reddy