Forum Discussion

BryonScruggs's avatar
BryonScruggs
Frequent Visitor
3 years ago
Solved

Filter Two unrelated Tables

I need to filter a table based on another table, however there is not a direct relationship between the two. Here is the data model.
attr_Tkpr_vw contains all company employees, both current and historical.
attr_Time_vw contains date/time info.

 

My goal is to filter attr_Tkpr_vw for all employees between arbitrary hire and termination dates. These dates are supplied by a slicer based on the column attr_Time_vw[DateFull]


Here are my results. My slicer is set to dates between 1/1/2022 and 3/31/2022. When I put the measues "MinDate" and "MaxDate" into a vizualization I get the expected results. However, when I try and use these measures to filter attr_Tkpr the formula returns a table with ALL dates.

 

 

I'm clearly missing something key about how filter contexts work.

Need Help
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    Hi, BryonScruggs 

    Based on your screenshot, you can see that your metric is DateTest= FILTER(....), which looks like a calculated table.

    For calculated tables, this is a row context, and there is no way to get the filter context of your slicer filter.

    If you want to use slicers, then you need to put your fields in the visual, and then write measures according to the conditions you want to display, return the corresponding values, and then put the measures on "Filter on this visual" to meet your needs.

     

    If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

    Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

     

    Best Regards,

    Aniya Zhang

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

     

2 Replies

  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi, BryonScruggs 

    Based on your screenshot, you can see that your metric is DateTest= FILTER(....), which looks like a calculated table.

    For calculated tables, this is a row context, and there is no way to get the filter context of your slicer filter.

    If you want to use slicers, then you need to put your fields in the visual, and then write measures according to the conditions you want to display, return the corresponding values, and then put the measures on "Filter on this visual" to meet your needs.

     

    If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

    Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

     

    Best Regards,

    Aniya Zhang

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

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    3 years ago

    Hi,

    If you wish to see data of any randomly selected dates, then we should create one row for each date between the hire date and termination date.  Are you amenable to this approach.