[Urgent] [Hard question] [Project Management] Only show relevant values in a range of time

[Anonymous]

I have two tables (task and date):

task table

Date table

They do not have relationship between them

My Virsualization : A matrix which shows the allocation% by person by date.

                Measure: If the date in the range of the task start date and end date, sum the allocation% of the tasks.

My problem: When I click the "4%" , it should only show the first line in the table below. Because it is the only line fits that spot. However, it shows all the lines related to "Frank".

problem

Please help me how could it only shows the relevant lines. Thank you!

[OwenAuger · Accepted solution]

Hi  @Anonymous 

With your data structured as it is, you can create a measure to use as a visual level filter on the table visual.

1. Create this measure:
   

   Task Date Filter = 
   VAR MinDate =
       MIN ( 'Date'[Date] )
   VAR MaxDate =
       MAX ( 'Date'[Date] )
   RETURN
       CALCULATE ( 
           INT ( NOT ISEMPTY ( task ) ),
           task[startdate] <= MaxDate,
           task[enddate] >= MinDate
       )
   

   This measure effectively applies the date filter from the Date table (defined by min & max date) to the task table, (in the same was as your existing measure does), and returns 1 if there are any rows of task that overlap this date range.
2. Add Task Date Filter as a visual level filter on the table visual, and set the filter to "Task Date Filter is 1"
3. Now selecting cells of the matrix visual will filter the table 

Attached PBIX illustrates this - I more-or-less recreated your model with the sample data you posted.

Regards,

Owen

[Anonymous]

Hi @OwenAuger ,
I am surprised you solved my question so quickly. I tired on my local file and it works! Thank you so much!