Forum Discussion

davidcc's avatar
davidcc
New Member
3 years ago

Filtering table by values from another, filtered table

I have two tables:

 

Table A

Contract (unique) | Date from  | Date to
2021              | 2021-01-01 | 2022-01-01
Q1 2021           | 2021-01-01 | 2021-04-01
Q2 2021           | 2021-04-01 | 2021-07-01
...

 

Table B

ID | Date       | ...
1  | 2021-03-01 | ...
2  | 2021-07-01 | ...
3  | 2021-02-08 | ...

 

When no Contract value is selected then all rows from Table B are shown. I would like to filter Table B by the "Date from" and "Date to" values in Table A when a single "Contract" value is selected. So, for example, when one selects "Q1 2021" then Table B values should be filtered to:

ID | Date       | ...
1  | 2021-03-01 | ...
3  | 2021-02-08 | ...

I'm imagining that I should add a date filter (after->before) on the Date column on Table B, but I don't know how to link it with the Date from and Date to in Table A.

 

Is there a way to do this in Power BI?

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi davidcc ,

    How about create relationships between the tables? What will the table B show when Table A "Contract" value is selected? 

     

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Polly

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

    • davidcc's avatar
      davidcc
      New Member
      3 years ago

      Thanks Anonymous, I have edited my question to make it a bit more specific.

  • ckl2022's avatar
    ckl2022
    Regular Visitor
    3 years ago

    quick way could be to create 2 calendar tables (calendar A for the "From" filter & Calendar B for the "To" filter)

     

    Create relationship between Calendar A & Table A (use Date From Column) 

    Create relationship between Calendar B & Table A (use Date To Column)

    then

    Create relatonship between Calendar A & Table B

    Create relationship between Calendar B & Table B

     

    Add two slicers to your visual, name the first "From Date" with Calendar A dates and the  other should be "To Date" with values from Calendar B.

     

    there is probably a more tidy way to do this,, but yeah - quick fix.