Filtering data based on another table's filtering which in turn is based on user selected value

[nilimarodrigues]

Hello Experts,

I am trying to find a solution for below problem but I am really not getting anywhere. Your help is sincerely appreciated.

So, I have three tables in my model - 1> Date Table 2> Configuration Table 3> Scenario Transaction Table. I have provided sample data for each of them below.

My objective is -> Based on user selected Month/Year, obtain the "Scenario Name" from the configuration table dynamically and use that "Scenario Name" to filter the data from table "Scenario Transaction Data".

For eg. If User selects 5/1/2020, The final table should display all three rows from "Transaction Data" for scenario "MAY SCENARIO" but the output should display the "Date" from "Date Table" for April, May and June/2020 as this "Date Table" is central table and have other time characteristics for further drill-ups like quarter, year etc.

If user enters -> 5/1/2020, output should be:

Thank You,

NR

[v-yingjl]

Hi @nilimarodrigues ,

Based on your description, you can create realationships between these tables without creating any measure:

Put the Date field in the slicer:

Use a table visual to show the result:

Attached my sample file that hopes to help you: Filtering data based on another table's filtering which in turn is based on user selected value.pbix

Best Regards,
Yingjie Li

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

[nilimarodrigues]

Relationship defintion as you have showen does not work in my business case as their were other tables involved as well causing relationship conflicts.

Unfortunately, this solution will work for a simple scenario. Did not work for my case and we have to remove user prompt of month to always use the scenario for current month.

[Greg_Deckler]

@nilimarodrigues - Seems like you could get there using LOOKUPVALUE or simple filtering (FILTER) wrapped with a MAXX for example. Or, if your relationships were:

• Date Table[Date] 1<->1 Configuration Table[Date]
• Configuration Table[Scenario Name] 1->* Scenario[Scenario Name]

Then it would just work.

[nilimarodrigues]

Thank you for your quick reply @Greg_Deckler .

I tried the relationship approach but unfortunately as I need to use dates from "Date Table" establishing relations as you mentioned is not working. It will only display me just one row from Transacction Data table for only the month selected by user.

I am not sure how can I use LOOKUPVALUE for this. Would you be able to help me understand how can I use it?