Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
mnb_dk
Helper I
Helper I

How 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 4
Syndicate_Admin
Administrator
Administrator

You don't need to create relationships in this case.

Hi

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
Administrator
Administrator

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.

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?

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.