Forum Discussion
Using a field in a visual to filter a different field.
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.
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.
2 Replies
v-yalanwu-msftCommunity Support
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.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.
