Forum Discussion

ThomasWeppler's avatar
ThomasWeppler
Icon for Impactful Individual rankImpactful Individual
2 years ago

Difficult task with date filters.

Hi Power Bi community

I have a somewhat difficult challenge with date filters.

I have three tables. 

A. A date table [Calender]

Date = the dates

B. A table with all the task a company have [Tasks]

Task_id = a uniq number for each task

Task_name = a name for each task

C. A status table that shows how the status of each task have changed [Status]

Task_id = an id that shows which task have changed its date

status_id = a uniq id for each of the four staus [1,2,3,4]

Status_history_id = a uniq id

Date a date for each time the staus was changed.


Where you can pick one or multiple statuses in a slicer and you can pick the max date in a slicer.

My problem is that the task and date table filters the status table, but not the other way around. So you can see a ton of tasks without a status. I tried to get around this problem by adding a filter to my report like this 

Max Status =
var _maxid = MAX('State'[Date])
return
IF(COUNT('Task'[Task_id]) = 1,
LOOKUPVALUE('State'[Status], 'State'[Date], _maxid),
BLANK())

 

This filter works, but makes it impossible to see multiple statuses at the same time.
Does anyone have an idea on what I should do next to get further with solving this problem? All help is greatly appriciated.

2 Replies