Using a Table to call value into a filter

[alwong]

Hi There. I hope you are having a good day. 

This is the context: The report i'm working on has "Sales Divisions" and each one is defined differently which requires me to filter them so I get the value I need. 

For example:

SalesDivision1: is filtered by a list of products (Up to a 100 products), and the way i'm currently doing it is by listing all 100 products in the DAX code using:

Keepfilters([Tablename][FieldName] in {Product1, Product2, Product3,...,ProductN})

This is for each of the Sales divisions I have aside from other filters each one has. 

I don't like the way this is coded since if I have to trouble shoot something in the code having too much information makes it difficult to find the error or if I have to change or add a product in the list.

What I want is to create a table containing the list of products to filter and use it instead of having the list specified in the code for each sales division. 

I hope I did a good work explaining my issue. I've been thinking on how to get it done but i'm relatively new on this. 

Thanks in advance. 

[GeraldGEmerick · Accepted solution]

@alwong Sure, you could do something like the following:

Product Table = 
UNION (
  GENERATE( { "SalesDivision1" }, {Product1, Product2, Product3,...,ProductN} ),
  GENERATE( { "SalesDivision2" }, {Product1, Product2, Product3,...,ProductN} ),
  GENERATE( { "SalesDivision3" }, {Product1, Product2, Product3,...,ProductN} ),
  ...
)

[v-tejrama]

Hi @alwong ,

Thank you @Ashish_Mathur and @GeraldGEmerick for the response provided!

Has your issue been resolved? If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

Thank you for your understanding!

[v-tejrama]

Hi @alwong ,

I wanted to follow up and see if you had a chance to review the information shared. If you have any further questions or need additional assistance, feel free to reach out.

Thank you.

[Ashish_Mathur]

Hi,

You could create a Sales division calculated column in t he Fact table to fetch data from the product table based on the Product lookup value.  You may se the LOOKUPVALUES()/CALCULATE()/RELATED() function to do so.  To receive more help, share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[GeraldGEmerick · Accepted solution]

@alwong Sure, you could do something like the following:

Product Table = 
UNION (
  GENERATE( { "SalesDivision1" }, {Product1, Product2, Product3,...,ProductN} ),
  GENERATE( { "SalesDivision2" }, {Product1, Product2, Product3,...,ProductN} ),
  GENERATE( { "SalesDivision3" }, {Product1, Product2, Product3,...,ProductN} ),
  ...
)

[lbendlin · Accepted solution]

@GeraldGEmerick   You can use CROSSJOIN  for that. 

@alwong you cannot dynamically create calculated tables. You can only create table variables inside measures.