Filter two periods with one slicer by due date

[Markus_R]

Hi,

i'm currently trying to create a report that displays the group of a user to a specified due date and the tasks he is working on at a moment.

For this i got four tables which somehow look like this:

In addition i've got a Calender Table, for which i created a Slicer so only one date can be selected.

The result that i want is a table which includes the Group, all users in the group which got a task assigned to them at the due date, and a column for each Task(1-3) (there won't be more then 3 tasks ever), where either a 1 or a 0 is displayed if the user is assigned or not.

For example (not related to the data supplied, just basic how it should look like):

If a user has no task assigned to the due date it should not get displayed.

Is there a way i can create a measure for that?

If needed i can make changes to the tables in PowerQuery too.

Thanks alot for your responses.

Markus

[amitchandak · Accepted solution]

No sure I got it completely. But you can use cross join to remove your date relation and userelation to use a relation.

You can have formula like

Due project =

var _max_date = max(selected(date[date])

calculate( count(project[id]),filter(project, start_date<=_max_date  && (due_date is null || due_date>_max_date )) ,<use crossjoin or filter to remove time join>)

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[Markus_R]

A little change on your formula did the trick for me.

Thank you very much!