How to make filter in calculate measure dynamic

[s082_sbux]

Hi,

I have a calculate measure to sum quantities of specific product code. Every 3 months new products need to be added in that list.

Is there any way make it dynamic instead of entering the product code inside IN need to update it in table.

 i don't want to use IN as i need to edit the product code between double quotes and comma where the list getting bigger make it difficult.

[Greg_Deckler · Accepted solution]

@s082_sbux Not unless you have some kind of flag or indicator on what to include. If you did then you could replace your manually typed table with somethig like:

SELECTCOLUMNS( FILTER( 'ilr', [Flag] = 1 ), "__Code", [Article Code] )

[Greg_Deckler · Accepted solution]

@s082_sbux Not unless you have some kind of flag or indicator on what to include. If you did then you could replace your manually typed table with somethig like:

SELECTCOLUMNS( FILTER( 'ilr', [Flag] = 1 ), "__Code", [Article Code] )

[s082_sbux]

Could you pelase helo me with what is flag as I'm very beginner 

[Anonymous · Accepted solution]

Hi @Greg_Deckler ,thanks for the quick reply, I agree with your thoughts and I'll add to them further.

Hi @s082_sbux ,

As Greg_Deckler said, you need a column of data to determine whether the current code needs to be calculated or not.

A value of 1 in the 'Flag' column means that the calculation is required, while 0 means that it is not required.

Please follow these steps:

1.Use the following DAX expression to create a table

Table = SELECTCOLUMNS(FILTER('ilr','ilr'[Flag] = 1),"Code",'ilr'[ Article Code])

2.Use the following DAX expression to create a measure

Measure = CALCULATE(SUM(ilr[Qty]),'ilr'[ Article Code] IN DISTINCT('Table'[Code]))

3.Final output

If you don't have a column to determine if you want to calculate the value of the Code, it's completely random and you'll have to add the Code manually.