Matrix rows filter breaks, and controlling the matrix columns

[dwarner]

Hi All,

I am struggline with the Matrix visualizer. 

Background: 

I'm developing a project management report.  Pulling data in from MS Project for the web.  So far so good.

I have a project slicer to select a specific project and a date slicer to select the reporting date.

I am using the project summary tasks to act as my project milestones, so ideally, I can show the period of time over which the milestones will be acheived.  

Details:
The rows for the matrix are pulled from the msdyn_projecttask table, the project slicer restricts the rows to the selected project and the filter to summary tasks. This is where the msdyn_summary value is true.  

Ideally I would like the columns to represent the weeks (by first Monday) listed before and after the reporting date.

i.e. if the slicer selected reporting date is 09-Jul-24, the columns would be:

17-Jun-24, 24-Jul-24, 01-Jul-24, 08-Jul-24, 15-Jul-24, 22-Jul-24, 29-Jul-24

Question 1) How do I establish the columns I desire?  As I attempt this I either get all the dates from my date table or super narrow, based on the start dates of the summary tasks.  My attemtps to build a filtered date table have failed so far

I've been struggling with creating the measure that would populate the matrix with values to create the values needed to do the conditional formating.  I'm pulling from many examples but with limited success. 

Question 2) When I add a measure to the Values tab it breaks the filter on my matrix rows.  Rather than just showing the summary project tasks for the specific project.  It shows all tasks for all projects.  Why is this happening?

Thank you in advance.

[Anonymous · Accepted solution]

Hi @dwarner ,

I’d like to acknowledge the valuable input provided by the @amitchandak . Their initial ideas were instrumental in guiding my approach. However, I noticed that further details were needed to fully understand the issue. 

1. Create a calculated column for your table to get the week start date of the date.

week start date = [Date] - WEEKDAY([Date],2) + 1

2. Create a table of dates and do not relate it to the previous table.

3. Create MEASURE.

MEASURE =
VAR _start =
EOMONTH ( MAX ( 'DAX DateTable'[Date] ), -2 ) + 1
VAR _end =
EOMONTH ( MAX ( 'DAX DateTable'[Date] ), 0 )
VAR _dates =
CALCULATE (
MAX ( 'Table'[week start date] ),
FILTER ( 'Table', 'Table'[Date] >= _start && 'Table'[Date] <= _end )
)
RETURN
IF ( MAX ( 'Table'[Date] ) = _dates, 1, 0 )

4. put the dates from the date table into slicer. put the measure into the filter of the matrix, filtering fields with a value of 1.

When adding a measure, it may be evaluated in a different context, causing the filter to be applied differently. Ensure that the scope of the measure is correctly limited to the specific project task.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@dwarner , Based on what I got you can have the week start date as Monday in your table

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)

And then use that in Matrix visual like what I have done here

Matrix as Project plan Visual: https://youtu.be/R25QoiyoSVs