Dynamic Grouping of Project Names (e.g., Test/Dev) Based on Quarter in Power BI Matrix Visual

Hey NagaPushpa ,

To dynamically group project names such as “Test/Dev” only for Q1, and keep them separate from Q2 onward in your Power BI Matrix visual, you can solve this entirely through DAX, without changing the model structure. 

1. Solution Using Calculated Column

Create a Calculated Column for Dynamic Grouping

In your Projects_Lookup table, create a new calculated column like this:

Project Grouping Dynamic =
VAR SelectedWeek = MAX('Calendar'[Week Starting])
VAR SelectedQuarter =
    LOOKUPVALUE(
        'Calendar'[Quarter],
        'Calendar'[Week Starting],
        SelectedWeek
    )
RETURN
SWITCH(
    TRUE(),
    SelectedQuarter = "Q1" && Projects_Lookup[Project Name] IN {"Test", "Dev"}, "Test/Dev",
    Projects_Lookup[Project Name] IN {"UAT", "AST"}, "UAT + AST",
    Projects_Lookup[Project Name]
)

However, since calculated columns are static and can't respond to slicers or dynamic context like quarters at runtime, the better approach is to use a DAX measure or a dynamic table with disconnected slicers. See next steps.

2. Solution Using DAX Measure with Dynamic Label

Use this instead of a calculated column, and apply this measure as your Row Header or Tooltip:

Dynamic Project Group =
VAR SelectedDate = MAX('Calendar'[Week Starting])
VAR SelectedQuarter =
    CALCULATE(
        MAX('Calendar'[Quarter]),
        FILTER(
            ALL('Calendar'),
            'Calendar'[Week Starting] = SelectedDate
        )
    )
RETURN
SWITCH(
    TRUE(),
    SelectedQuarter = "Q1" &&
    SELECTEDVALUE(Projects_Lookup[Project Name]) IN {"Test", "Dev"}, "Test/Dev",
    SELECTEDVALUE(Projects_Lookup[Project Name]) IN {"UAT", "AST"}, "UAT + AST",
    SELECTEDVALUE(Projects_Lookup[Project Name])
)

Then, add this as a new row header field in the matrix visual, instead of the original project name.

3. Solution Using Lookup Table + DAX

To handle many such rules across quarters:

1. Create a table like ProjectGroupingRules with:

   • Project Name

   • Quarter

   • Group Name

2. Then in DAX, use LOOKUPVALUE to dynamically fetch the group name for the selected project and quarter.

Dynamic Group Name =
VAR ProjectName = SELECTEDVALUE(Projects_Lookup[Project Name])
VAR Quarter = CALCULATE(MAX('Calendar'[Quarter]))
RETURN
LOOKUPVALUE(
    ProjectGroupingRules[Group Name],
    ProjectGroupingRules[Project Name], ProjectName,
    ProjectGroupingRules[Quarter], Quarter
)

This lets you manage the logic from a table no code changes needed later!

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam