Dynamic Column Categorization among multiple levels of columns in PowerBI Matrix

[mihirs]

I have following matrix which has two levels of columns, MonthPassed & Fiscal Months (July to June) :

All the months that have completed prior to current month need to be categorized into Actuals and remaining ones into Budgets by default. This categorization is the first level of columns and month is the second level as you can see in the image above. 

What i want is to give user the slicer for the month so that the user can select the month of his choice and the months till that selected month (including) shall dynamically be categorized into Actuals and remaining ones into Budgets. Meaning Currently it's december so when user opens this dashboard, months prior to december will be under Actuals and remaining ones under Budgets.

But let's say user selects month Aug then all months till Aug shall be under Actuals and remaining (Sept onwards) under Budgets. So there should be dynamicity in the levels of columns that I'm needing.

My dim_dates table which is having the columns MonthPassed & FiscalMonth looks as below.

Let me know if there is a way through field parameter or measure(not allowed in column section of matrix) or calculated column through which this can be achieved.

[burakkaragoz]

Hi @mihirs ,

This is a classic Power BI challenge! You have hit a fundamental rule of DAX: Calculated Columns are computed at "Refresh Time", but Slicers operate at "Query Time".

This means a Calculated Column (like your MonthPassed) cannot physically change its text from "Actuals" to "Budgets" based on what a user selects in a slicer. Once the data loads, that column is frozen.

The Solution: The "Scaffold" Method To achieve this dynamic grouping, we need to trick the Matrix. We will create a separate table that contains all possible combinations of Months and Categories, and then use a Measure to hide the invalid ones.

Here is the step-by-step approach:

1. Create a "Cutoff Slicer" Table You need a disconnected table (no relationship to your main model) for the user to pick the cutoff month.

• Table Name: CutoffSelection

• Column: Month Name (and Month Sort)

2. Create a "Scaffold" (Header) Table We need a table that will serve as the columns for your Matrix. It needs to have every month listed twice: once for "Actuals" and once for "Budgets". You can create this in DAX:

Kod snippet'i

MatrixHeaders = 
UNION(
    ADDCOLUMNS(DISTINCT(dim_dates[FiscalMonthName]), "Type", "Actuals", "Sort", 1),
    ADDCOLUMNS(DISTINCT(dim_dates[FiscalMonthName]), "Type", "Budgets", "Sort", 2)
)

Use this table's Type and FiscalMonthName in your Matrix Columns.

3. The Magic Measure Now, we write a measure that checks the user's selection. If the month falls into the wrong category, we return BLANK(). The Matrix visual will automatically hide columns where the measure is blank.

Kod snippet'i

Dynamic Value = 
VAR SelectedCutoffIndex = MAX('CutoffSelection'[Month Sort]) -- Get slicer value
VAR CurrentMonthIndex = MAX('MatrixHeaders'[Month Sort])     -- Get column context
VAR CurrentType = MAX('MatrixHeaders'[Type])                 -- Get group context ("Actuals" or "Budgets")

RETURN
SWITCH( TRUE(),
    -- Logic for Actuals: Only show if month is before or equal to cutoff
    CurrentType = "Actuals" && CurrentMonthIndex <= SelectedCutoffIndex, [Your Amount Measure],
    
    -- Logic for Budgets: Only show if month is after cutoff
    CurrentType = "Budgets" && CurrentMonthIndex > SelectedCutoffIndex, [Your Amount Measure],
    
    -- Otherwise return Blank (This hides the column)
    BLANK()
)

How it works:

1. The Matrix effectively tries to render every month under "Actuals" and every month under "Budgets".

2. The measure checks the Slicer.

3. If you select "August", the measure returns BLANK() for "September Actuals" (hiding it) and returns a value for "September Budgets" (showing it).

It is a bit of a workaround, but it gives you exactly the dynamic column categorization you are looking for!

---

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was assisted by AI for translation and formatting purposes.

[mihirs]

Thank you @burakkaragoz . That takes me very close to the solution but, I forgot to mention one more thing that under the values section of this matrix I've below two measures (one of those I've hidden in matrix bcoz they are used for sorting purpose) shown in image:

With your solution I'll have Amount as Blank for relevant months and but they would still have value in sorting measure and that would not let us achieve end outcome. This sorting measure is added by me to keep the Rows exactly in order the client wants since in matrix order can't be done by rows it needs to be done either by column or values so i had to add them under values section and then hide them. Let me know if there is a workaround for this. Thanks for your help.

[burakkaragoz]

Ah, the "Hidden Measure" trap! You figured out exactly why it is happening.

The Rule: For a Matrix column to automatically collapse (hide), EVERY measure inside that column context must return BLANK(). Currently, your Amount returns Blank (correct), but your SortingMeasure returns a number. So the Matrix thinks: "There is still data here, so I must show this column!"

Here are two ways to solve this:

Option 1: The Quick Fix (Wrap the Sorting Measure) You need to apply the exact same "Cutoff Logic" to your SortingMeasure as well. Even though it is hidden, it needs to return BLANK() when the date criteria are met.

Create a new version of your sorting measure:

Kod snippet'i

Dynamic Sorting Measure = 
VAR SelectedCutoffIndex = MAX('CutoffSelection'[Month Sort])
VAR CurrentMonthIndex = MAX('MatrixHeaders'[Month Sort])
VAR CurrentType = MAX('MatrixHeaders'[Type])

-- Check the logic first
VAR ShouldShow = 
    (CurrentType = "Actuals" && CurrentMonthIndex <= SelectedCutoffIndex) ||
    (CurrentType = "Budgets" && CurrentMonthIndex > SelectedCutoffIndex)

RETURN
-- If it should show, return the sort value. If not, return BLANK to hide the column.
IF(ShouldShow, [Your Original Sorting Measure], BLANK())

Replace your current sorting measure in the visual with this new one.

Option 2: The "Best Practice" Way (Sort By Column) Actually, you usually don't need a hidden measure in the Values well to sort rows.

1. Go to your Account Group dimension table.

2. Make sure you have a numeric column (e.g., AccountGroupIndex) that defines the order.

3. Select the Account Group text column -> Go to Column Tools ribbon -> Click Sort by column -> Select the Index column.

This way, the Matrix will naturally sort your rows based on the model, and you won't need that extra measure cluttering your visual!

Hope this clears it up!

---

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was assisted by AI for translation and formatting purposes.

[mihirs]

For option 2, it's showing error when I'm trying to sort Account Group by Index column. The reason is Account Group has repeating values and is not a distinct column so it can't be sorted by Index column. Also in my matrix there are three levels in the rows, Account Group, Account Subgroup and Description. Among these Acc Group and Subgroup have repeating values in table. But Description has distinct values and when I sort it by Index, still the order changes, it doesn't remain as per the image i've shared in earlier reply. Here the ordering needs to be preserved at all three levels ,i.e. Acc. Group, Subgroup and Description.

[burakkaragoz · Accepted solution]

I see the issue now. The "Sort by Column" error confirms that your table has duplicate values for Account Group, and likely the granularity isn't strictly 1-to-1 with your Index. Cleaning up the data model to a pure Star Schema (with unique Dimension tables) is the long-term fix, but I understand you need this working now.

So, let's go back to Option 1 (The Hidden Measure).

You mentioned a concern: "With your solution... they would still have value in sorting measure."

Actually, the logic I proposed handles exactly that. The key is to force your SortingMeasure to return BLANK() for the exact same months where your Amount is blank.

Here is the logic:

1. For Visible Months (e.g., July):

   • Amount = $1000

   • Sorting Measure = 1

   • Result: Column is visible, and rows are sorted correctly by the '1'.

2. For Hidden Months (e.g., Future Months):

   • Amount = BLANK

   • Sorting Measure = BLANK (Because we wrap it in the IF logic)

   • Result: Since both measures are blank, the Matrix collapses (hides) the column.

You do not need the sorting measure to return a value for the hidden months. You only need it for the visible ones.

The Fix: Wrap your existing sorting logic in the same condition you use for your values.

Dynamic Sorting Measure = 
VAR IsVisible = [Your Logic To Determine Actuals vs Budget] -- e.g., Is this month visible?
RETURN
IF(
    NOT ISBLANK(IsVisible), -- Only return a sort value if the column is meant to be shown
    [Your Original Sorting Logic], 
    BLANK()
)

Once you do this, the "future" columns will be completely empty (no Amount, no Sort value), and the Matrix will automatically hide them. The "current" columns will still have the sort value, so your row order (Account Group > Subgroup > Description) will be preserved perfectly.

Give this a try, it should solve both the hiding issue and the sorting requirement!

---

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was assisted by AI for translation and formatting purposes.

[amitchandak]

@mihirs , Here you might need a disconnected table for slicer. In that manner you can Switch the measure.

Date 1 is disconnected date table and Date is connected  then you can have new measure assume you already have Actual and Budget measure 

= Calculate([Actual], filter(Date, Date[Date] <= Max(Date1[Date]) ) ) +  Calculate([Budget], filter(Date, Date[Date] <> Max(Date1[Date]) ) )