Dynamic content filters based on UserId, StartDate, EndDate and possible movement between groups.

[Shaun2020]

Hi, I need some help.

My tables are currently in Excel, I plan to migrate to Power Bi, although the Power Query should be the same or very similar.
I have a dataset,  that consists of two source tables from different systems, a date table(I know it's not exactly required anymore) and a Users table, where I want to filter on specific rows, UserID, StartDate and EndDate. There is also a Group column

Ideally I would like to use the Users table as the source of the filter as it is easier updating this table.

My datasets currently have 35 Users of which I filter on 30 users. I currently hard code this in my filters. I am told this will be growing soon.

When a user moves between one of the three groups, I can add the condition as their data is already included in the dataset from the beginning of the year. Also I have to hard code when they either left or joined any of the groups. The number of groups will be increasing too, I believe.

I have an idea that I should create a function and pass this as the UserId filter, also for the StartDate and EndDate.

The most critical issue I think is where the user moves between groups.

I have searched but have not found a solution to how to do this. I can only get it to work with hardcoded values in the query.

I can use a join to filter out UserID's I don't need in the dataset, but where the users shift between groups I have no idea how to pass this as a function as they will end on one date and start on another. I have not yet seen this happen on the same day.

Anyone that can help me figure this out please?

S

[v-yanjiang-msft]

Hi @Shaun2020 ,

Sorry I'm not very clear about the problem, could you please give an example to clarify it?

Best Regards,
Community Support Team _ kalyj

[Shaun2020]

Hi

I'm trying to make a hard coded filter dynamic. The variables are UserId, StartDate, EndDate, Group

If there is an end date and the user is not moved to a different group then their data should be filtered out as they may have moved completely to another department that I don't report on.

Does this make any sense?

My User table will look like this 

UserId	FullName	Group	StartDate	EndDate
GeorgB	George Budge	Group1	01/01/22
MaryM	Mary Murry	Group1	01/01/22	10/06/22
FredF	Fred Flintsone	Group2	01/01/22	24/08/22
CarlM	Carl Mills	Group3	01/01/22
MaryM	Mary Murry	Group3	11/06/22

Sorry, this table does not display correctly.

The other two tables of data all have the UserId which is common, also an activity date, so I'm currently linking the tables and using RELATED or RELATEDTABLE, (Sorry I'm writing this from memory as I'm on a different task right now.) to add the FullName and was adding the Group, but now have people being shifted between groups. The Users table consits of three group tables that I have appended in PQ for ease of use.
I am wanting to change my report to update according to the user table values.

The report itself uses slicers to select a month or a range of months and the group to show the users, the group they are in and their activities, which are using measures in a couple pivot tables. I can also select for which group I wish to see activities. For Management they may need to see all the groups and all months selected.

I can see this being a problem where a user has shifted in the middle of a month or more than a single month is selected as the user will then appear twice, as they will be in different groups.

I would like to find a solution to do this as I was told yesterday that the users and groups I report on will be doubling and there will be more restructuring and users being moved around.

I can see that I will have to move to Power Bi going forward.

So for UserId I need to create a parameter, also for the start and end date. If the user appears more than once, the date must be checked and the group during that selection and so on.

Does this help?