DAX - Calculate volume once, ignore duplicates

[carlenb]

Hi, 

I have the following scenario that I need help with in DAX for Power BI. 

Column A = Article nr. There are duplicates here but I don't want to remove them in Excel or Power Query

Column B = Price

Column C = Quantity

DAX Column = New column that I need to calculate B*C, but only once per article nr (the first value is ok) and then return 0 if column A contains duplicates. 

Thanks! Let me know if I need to clarify. 

[carlenb]

Thanks a lot! I got that to work, very helpful.

Still a learner in this. How can I tweak thi if I only want to get the quantity in the column? See example below. 

[ChiragGarg2512]

In the First Qty measure, change 

'Table'[Qty]*'Table'[Price]) to  'Table[Qty]  in calculate of return statement.

First Qty = Var _min = MINX(filter( ALLSELECTED('Table'), 'Table'[Article nr] = min('Table'[Article nr])), 'Table'[Index])
return 
CALCULATE(sumx('Table', 'Table'[Qty]*'Table'[Price]) , filter( 'Table', 'Table'[Article nr] = min('Table'[Article nr]) && 'Table'[Index] = _min) )

[carlenb]

Thank you! 

I actually get an error here, saying that SUMX doesn't work with that type of string. Any ideas? Does that work for you? 

[ChiragGarg2512]

Yes, because there is typo is the last reply it has to 'Table'[Qty] instead of 'Table[Qty]. It should work correctly now.

If it still doesn't work send a screenshot with the error.

[carlenb]

Still an error. I do want to keep the first two measures for volume, so I've created two new measures for quantity. So what I have now is: 

Qty Only = Var _min = MINX(filter( ALLSELECTED('Table'), 'Table'[Article nr] = min('Table'[Article nr])), 'Table'[Index])
return 
CALCULATE(sumx('Table', 'Table'[Qty]) , filter( 'Table', 'Table'[Article nr] = min('Table'[Article nr]) && 'Table'[Index] = _min) )

and

Sum Qty Only = sumx(VALUES('Table'[Article nr]) , [Qty Only]) 

Here is the error message (not in English) but I've highlighted the code.

[ChiragGarg2512]

The DAX used is correct, there is a uncertainity on what is creating the issue. 

Here is the .pbix file.

sol.pbix

[carlenb]

Thank you, I will have a look. Appreciate all the help!

Another thing I ran into is that the pbix file size increased from 1,3 mb to over 28 mb when I created the measures. So it takes some time for the graph to load. The dataset includes more than 20 000 rows but should it really become that big because of two measures? Any advice?

[ChiragGarg2512]

@carlenb I am sorry, I can't help you with that as I a pretty new to powerBI myself. A measure shouldn't affect the memory as much because it doesn't hold any value and calculates the value at the moment. Visuals and extra columns can affect the data.

If you found my reply helpful, please mark it as a solution.

Thank You. 

[ChiragGarg2512]

Hello , First go to power Query and add an index column.

then create two measures with the following DAX:

First Qty = Var _min = MINX(filter( ALLSELECTED('Table'), 'Table'[Article nr] = min('Table'[Article nr])), 'Table'[Index])
return 
CALCULATE(sumx('Table', 'Table'[Qty]*'Table'[Price]) , filter( 'Table', 'Table'[Article nr] = min('Table'[Article nr]) && 'Table'[Index] = _min) )

Sum First Qty = sumx(VALUES('Table'[Article nr]) , [First Qty]) 

 For more information refer to thses blogs:

https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee8...

@carlenb