This must be straightforward, but for the life of me I haven’t found the answer.
My dataset involves store sales of different products. I want to show graphs of sales for different types of products in different stores.
Stores are identified by a store id, and have a store name.
Products are identified by a product id, and have product names.
I get the graphs that I want, but the controls show the store ids and the product ids, because that is the parameter that is used in the joins everywhere to retrieve the relevant data.
However, these controls are not useful to my client, because there are hundreds of stores and thousands of products. They should instead be able to see in the control dropdowns, the store names and the product names.
My problem is then: how do I show the names in the controls, but then have the graphs update by the ids? Some sort of calculation that says if control selects store ABC, then go lookup what id that is then send that to the dataset to update the graph?
Something like a lookup or vlookup calculated field I guess.
Appreciate any help, cheers!