Forum Discussion
Helper IHow to create a "reference table" fo use as filter?
Hi
I often need to limit a report to only show values for specific product numbers.
Until now I have used DAX and made a calculation, however I'm not sure this is best-practice. Especially not if it includes a lot of product numbers.
Example:
Product line X = CALCULATE([Sales Orders (Current Sales Orders)];FILTER(Product_list;Product_list[Product_No] IN {"1223"; "4232"; "7354"; "1645"}))
Is it possible to add a table in Power Query only containing the specific product numbers and somehow link this and use it as a filter on the total product number table?
4 Replies
Syndicate_AdminAdministrator
You don't need to create relationships in this case.
mnb_dkHi
I followed the steps you provided above by insterting a table and changed the DAX measure. It works.
However, when I click on the graph that contains sales orders from the specific product numbers, the other visuals don't change/filter. How come?
- Syndicate_Admin
you could create a table manually from the "Specify Data" option in the Start menu with a column that has the codes you need.
Then you modify your measurement, something like this:
Product Line X = CALCULATE('Sales Orders' (Current Sales Orders), filter(Product_list, Product_list[Column Name] in VALUES('Auxiliary Table'[Name: Auxiliary Lumuna])))Auxiliary Table and Auxiliary Columa Name correspond to the manually created table.- mnb_dk
Hi
Thanks for quick reply.
Do I have to link the table in the modelling tab or is this not needed?
Also, I would like to be able to filter the other visuals based on the "specific style table". Will this be possible with the solution you suggest?