Forum Discussion

o59393's avatar
o59393
Post Prodigy
3 years ago
Solved

Calculated column for duplicate values

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.

 

 

 

 

Need Help
Show and Tell
  • Ashish_Mathur's avatar
    Ashish_Mathur
    3 years ago

    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.

     

3 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    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.