Calculated column for duplicate values

[o59393]

Hi all

I need a calculated column that sums the distinct count values in the 'Pack' column for the concat of the columns 'Location' and 'Manufacturing line'.

To give an example here is the following table:

Location XX and Line 1 has just one type of pack (NR), therefore the result should be 1 (column F)

Location XX and Line 2 has just two types of pack: NR and Ref, therefore the result should be 2 in column F.

How can i replicated this excel formulas into just one calculated column in Power BI?

Excel attached:  Duplicate.xlsx

Thanks.

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works

Column = CALCULATE(DISTINCTCOUNT(Data[Pack]),FILTER(Data,Data[Location]=EARLIER(Data[Location])&&Data[Manufacturing line]=EARLIER(Data[Manufacturing line])))

Hope this helps.

[o59393]

Amazing @Ashish_Mathur 

You are the best.

Thanks a mill.

[Ashish_Mathur]

You are welcome.  Thank you for your kind words.