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

[NagaPushpa]

Hi Community,

I’m working on a Power BI report that involves a matrix visual with the following structure:

• Rows:

  • Project (from Projects_Lookup table)

  • Location (from Location lookup table)

• Columns:

  • Week Starting (from Calendar table)

• Values:

  • Forecasted Utilization % (measure from Data table)

  • Actual Utilization % (measure from Time Entries table)

  • Deviation (custom measure)

---

Current Setup:

• The Projects_Lookup table has a Project Name column with values like:
  Test, Dev, Production, Intermittent, UAT, AST, Final

• Previously, I had a requirement to group Test and Dev into a single label: "Test/Dev". I achieved this using a calculated column:

  DAX

  NewProjectName =
  SWITCH( TRUE(),
  Projects_Lookup[Project Name] IN {"UAT", "AST"}, "UAT + AST",
  Projects_Lookup[Project Name] IN {"Test", "Dev"}, "Test/Dev",
  Projects_Lookup[Project Name] )

• This worked fine until now.

---

New Requirement:

Now I have a new requirement where:

• "Test/Dev" should be grouped together only for Q1.

• From Q2 onward, "Test" and "Dev" should appear as separate entries.

• This needs to be dynamic, as similar changes may occur in the future (e.g., grouping or ungrouping for specific time periods).

---

What I’ve Tried:

• I created a new bridge table:
  DistinctProjectLookup = DISTINCT(Projects_Lookup[Project Name])
  and added necessary relationships. It works for separating all projects correctly.

However, I’m struggling with implementing the dynamic grouping logic based on quarter (Q1 vs Q2 and beyond).

---

Ask:

• How can I dynamically group or ungroup project names based on the selected quarter or based on the Week Starting date in the visual?

• Should I modify the model structure, relationships, or use a more advanced DAX pattern?

• How do I handle this kind of logic going forward in a scalable way?

Any help, guidance, or DAX pattern recommendations would be greatly appreciated.

Thanks in advance!

[v-echaithra]

Hi @NagaPushpa ,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,
Chaithra.

[v-echaithra]

Hi @NagaPushpa ,

As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.

If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.
If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.

Thank you for your patience and look forward to hearing from you.
Regards,
chaiithra

[v-echaithra]

Hi @NagaPushpa ,

As we haven’t heard back from you, 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,

Chaithra.

[Nasif_Azam · Accepted solution]

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