Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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] )
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.
@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] )
Could you pelase helo me with what is flag as I'm very beginner
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
26 | |
20 | |
14 | |
8 |
User | Count |
---|---|
75 | |
50 | |
47 | |
17 | |
17 |