Display Filter fields dropdown from lookup columns

[aaroh_bits]

Hi,

Dataverse table structure

• I have a Dataverse table called Request which has many lookups.
  
  One of columns is Capex  Category lookup, Opex  Category lookup & Expense Nature lookup columns as shown:
  
  
  
  
• This Capex category column and Open Category column from Request table is related to related table called Expense Nature Category table as shown:
  
  
  
  
• The Expense Nature Category table has following 1 column called Category  
  and Data is as follows
  
• Expense Nature table (3) is another lookup (besides Expense Nature Category table) with following schema
  
  
  With 1 single column called Expense Nature and data fields as follows
  
  

Objective  (Power Apps Form)

I need to filter only entries from Capex Category only fields which are Capex

But dropdown is filtering all the fields shown up (Opex and Capex)
Similarly , I need to filter only entries from Opex Category only fields which are Opex

How could I filter fields only for Capex Category from dropdown which are Capex?
Please advice.

@v-xiaochen-msft @v-yutliu-msft @v-xida-msft 

[v-albai-msft · Accepted solution]

Hi @aaroh_bits ,

What is the “Related table” of your Expense Nature lookup column from Request(1st) table?

Is Expense Nature column from Expense Nature Category(2nd) table a lookup column related to your third Expense Nature table?

Do you want to filter choices from below Expense Nature Category column based on the value of below Expense Nature column, which is a lookup column related to your 3rd table)? For example, if filter condition is “Opex”, then the dropdown should have one choice:”Health,Safety&Environment Expense”.

If yes, I did a test on my side for reference. I assume that:

1. Your Expense Nature lookup column from Request(1st) table is related to Expense Nature Category table.

2. Your Expense Nature column from Expense Nature Category(2nd) table is a lookup column related to your 3rd Expense Nature table.

So, set Items property of your “Capex Category” dropdown to:

Filter('Expense Nature Categories','Expense Nature Categories'[@'Expense Nature'].'Expense Nature (cre5c_name)'="Capex")

And also, set Items property of your “Opex Category” dropdown to:

Filter('Expense Nature Categories','Expense Nature Categories'[@'Expense Nature'].'Expense Nature (cre5c_name)'="Opex")

Notes: Since the name of your 3rd table is same as your column name(both are Expense Nature), you need to use the @ disambiguation operator to distinguish between table name and  column name. For more information about Disambiguation inside Table, you can refer to this article.

Check the results below.

This is the default choices:

After changing. For Capex Category dropdown:

For Opex Category dropdown:

Best Regards,

Allen