If Duplicate Value In Column vs Distinct Occurrence

[AudreyPowerBI]

Good evening Power BI Community!

In the Query Editor, I'm trying to create a new custom column to do the following:

If the data in Column A only occurs once in that column, then show me what is in Column D, if the data point in Column A has a duplication in that column, then produce string "Multiple".

Note limitation: Removing duplicates in Column A is not an option in our case.

What would you suggest I use as the DAX formula here?

Please advise? Thank you kindly,

Audrey

[AudreyPowerBI · Accepted solution]

Hello and thank you everyone for your late night help!

It was definitely much appreciated. 😄

In case you're wondering, the approach I took that did the trick was as follows:

SOLUTION = IF (CALCULATE (HASONEVALUE(SOLUTION[COLUMNB]),ALLEXCEPT(SOLUTION,SOLUTION[COLUMNA]) )=TRUE,SOLUTION[COLUMND],"Multiple")

Thank you all and I hope you have a lovely weekend!

Audrey

[Vvelarde]

@AudreyPowerBI

Hi,in Query Editor you can do it following these steps:

Let mw know if you need more help

Regards

Victor

Lima - Peru

[Ashish_Mathur]

Hi,

Try this

=IF(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Heading1]=EARLIER(Data[Heading1])))>1,"Multiple",Data[Heading])

I have assumed that the Table name is Data and heading of column A id Heading1.  Also, the data type of entries in the heading column has been assumed as text.

[TomMartens]

Hey,

this DAX statement creates a calculated column in your table

var whatsincolumnD =  'yourtable'[ColumnD]
return
IF(
CALCULATE(
COUNTROWS('yourtable')
,ALLEXCEPT('yourtable', 'yourtable'[ColumnA])
) >1
,whatsincolumnD
,"Multiple"
)

Hopefully this is what you are looking for

Regards

Tom

[AudreyPowerBI]

Hello Tom,

Thank you for taking the time to respond!

I'm not familiar with DAX at all but am wondering, should I still use this formula even though I'm not trying to calculate anything?

Audrey