Using a field in a visual to filter a different field.

[maryhillegass]

Hello,

So let's say I have the following dataset. 

Batch	Component Batch	KGs
C123	B456	2500
B456	A789	2600
A789	RM0001	1300
A789	RM0002	1300

I want to build a report such that when I "lookup" C123 I am can see that 1300kgs of RM0001 and RM0002 were used. 

Does anyone know how to go about building something like this?

My initial thought was to build a report that would show the following tables, but I have only been able to do this by entering the component batches into a new batch filter manually. I would like to be able to tell the second table to use the value from the first table component batch field as the filter for the batch field of the second table. Is that possible?

Finished Batch 

Batch	Component Batch	KGs
C123	B456	2500

Intermediate Batch

Batch	Component Batch	KGs
B456	A789	2600

Raw Materials

Batch	Component Batch	KGs
A789	RM0001	1300
A789	RM0002	1300

Any help would be appreciated. I tried to look this up, but I did not find anything similar. 

[v-yalanwu-msft · Accepted solution]

Hi, @maryhillegass ;

You could duplicate three same table in power query.

Then create relationship about this three table,

The final show:

Of course you can also use your three tables:
Finished Batch 、Intermediate Batch、Raw Materials
Then set up the following relationship like I did.

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[maryhillegass]

@v-yalanwu-msft Is there a way to do this without the duplicate tables?

The tables in my dataset would be fairly large and I wouldn't want to have to store them 3 times if it was not necessary. This would accomplish what I was originally asking.