Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Icebear
Frequent Visitor

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

Table1:

member.id     date                               hours        
john22.2.2023 09:00:006
john3.5.2022 11:00:005
sue4.6.2019 08:30:0012
sue7.8.2021 09:00:003
sue12.3.2022 10:00:005

 

Table2

member.alias                 datestart                   dateend                       group            
john1.1.2022 08:00:001.8.2022 08:00:00group1
john2.8.2022 08:00:00 group2
sue1.6.2019 08:00:0014.3.2022 08:00:00group1

 

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?

1 ACCEPTED SOLUTION
v-jianboli-msft
Community Support
Community Support

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])

vjianbolimsft_0-1686639245086.png

Final output:

vjianbolimsft_1-1686639322496.png

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.

View solution in original post

4 REPLIES 4
v-jianboli-msft
Community Support
Community Support

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])

vjianbolimsft_0-1686639245086.png

Final output:

vjianbolimsft_1-1686639322496.png

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
Super User
Super User

@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
Super User
Super User

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 


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?

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors