Date filter works on TextColumn1 but needs to consider DateColumn in another table

[Icebear]

Table1:

member.id	date	hours
john	22.2.2023 09:00:00	6
john	3.5.2022 11:00:00	5
sue	4.6.2019 08:30:00	12
sue	7.8.2021 09:00:00	3
sue	12.3.2022 10:00:00	5

Table2: 

member.alias	datestart	dateend	group
john	1.1.2022 08:00:00	1.8.2022 08:00:00	group1
john	2.8.2022 08:00:00		group2
sue	1.6.2019 08:00:00	14.3.2022 08:00:00	group1

Table1 and Table2 have relationship: member.id to member.alias 

All date columns are type: datetime

-------------------

I have a filter1 that uses: date, in Table1.

☑ When I select year 2019 I see: sue

☑ When I select year 2022 I see: sue and john

☑ When I select year 2023 I see: john

I have another filter (filter2) on the same page that uses: group, in Table2.

☑ When I select year 2019 I see: group1 in filter2

☑ When I select year 2022 I see: group1 and group2 in filter2

☒ When I select year 2023 I see: group1 and group2 in filter2

This is incorrect, I only want to see group2 for the year 2023.

But because John also has an older registration for group1, both groups appear in filter2.

So the scenario I want: When I select year in filter1 I want filter2 to only display groups for the selected year.

Can anyone assist?

[v-jianboli-msft · Accepted solution]

Hi @Icebear ,

Please try to apply this measure to the visual's filter:

Flag = 
var _a = ADDCOLUMNS('Table2',"Flag",IF(ISBLANK([dateend]),COUNTROWS(FILTER(SELECTCOLUMNS('Table1',"Date",[date]),[Date]>=[datestart])),COUNTROWS(FILTER(SELECTCOLUMNS('Table1',"Date",[date]),[Date]>=[datestart]&&[Date]<=[dateend]))))
return SUMX(_a,[Flag])

Final output:

Best Regards,

Jianbo Li

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

[NaveenGandhi]

@Icebear 

Unfortunately there is no standard solution that fits to all in this situation.

It depends on how your data is, If you can share you data or PBIX after removing sensitive information, That will be useful to take a look into it further.

If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!

[NaveenGandhi]

Hello @Icebear 

The tables you have above will cause a many to many relationship. SInce "John" has both group 1 and group 2 in table 2, You will see group 1 and group 2 when you select him.

You should try to create a unique Primay key in table 2 that you can use in table 1.

Let me know if this helps!

If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!



Try to create the 

[Icebear]

Thanks for the reply NaveenGandhi.

I can't see another relationship option between the two tables 😕

Do you have any other suggestion for me to tackle this problem?