Lookup function?

[Class66Loco]

Hello,

Table A a Project ID column and Table B has a column which references the Table A Project ID column. Its a one to many relationship where the Project ID is listed once in Project A and is listed multiple times in Table B. Both tables have date columns with Table B the date being the date of the record. 

Power BI visual is configured to display Table A data using a data slicer (This uses calendar table based on the Table A date range).

Is it possible to display in a visual the Table B data which falls within the date slicer selection for Table A? There are instances where there are more than one Table B item for a selected date.

Thanks

[v-tsaipranay]

Hi @Class66Loco ,

Thank you for reaching out to the Microsoft fabric community forum and sorry for the delay response. Also thank you @Deku  and @Khushidesai0109  for your inputs.

Yes, it is possible to display Table B data filtered by the date slicer that is based on Table A, and have it work seamlessly with drillthrough as well. To achieve this, ensure both Table A and Table B are connected to a shared Date table through their respective date columns. This allows the slicer to filter both tables consistently. Since Table B is already related to Table A through Project ID, any drillthrough action that passes a specific project context will automatically apply to Table B as well.

If you're not using an active relationship between the Date table and Table B, you can create a measure that manually filters Table B based on the selected date range from the slicer, using MIN and MAX on the Date table. This setup ensures your visual reflects only the relevant Table B records based on both the date selection and drillthrough context, resolving the filtering issue you're encountering.

I hope my suggestions give you good idea, if you need any further assistance, feel free to reach out.

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

Thank you. 

[v-tsaipranay]

Hi @Class66Loco ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[Class66Loco]

Thank you @Deku 
The other visuals on the screen are from drill through so they reference a selected record from the previous screen. Can I set this visual up the same way? Thanks again for your help.  

[Deku]

Hard to say without seeing

[Khushidesai0109]

@Class66Loco 
Filtered Table B Measure =
VAR SelectedMinDate = MIN('Table A'[Date])
VAR SelectedMaxDate = MAX('Table A'[Date])
RETURN
IF(
SELECTEDVALUE('Table B'[Date]) >= SelectedMinDate &&
SELECTEDVALUE('Table B'[Date]) <= SelectedMaxDate,
1,
0
)

[Deku · Accepted solution]

If you add a date dimension and connect to both tables, when you filter the date in the date dimension both tables will be filtered to the same date range