Find Unique values in column

[Anonymous]

Hi all,

I'm trying to check whether the same value(number format) has occurred previously in the column, and not whether it occurs subsequently, I only want it to look up the column, not down the column.

=IF(COUNTIF(N$1:N14,N15)>0,0,1) - this is the formula in Excel, that is doing that.

can you please help me figure out how to do it in Power BI.

Thanks

Ket

[v-danhe-msft]

Hi @Anonymous,

Could you please offer some sample data and post your desired result if possible?

Regard,

Daniel He

[Anonymous]

Hi @v-danhe-msft,

please see the picture below, if the Payment ID appears in the list for the first time put 1 in Unique Payment column, if not then 0,

I highlighted rows for better understanding.

The solution that @Zubair_Muhammad suggested works if ill reduces the size of the data, but I need it to work on a large data.

Thanks   

[v-danhe-msft]

Hi @Anonymous,

Based on my test, I also need to add an index column to solve your problem and I suggest you refer to Zubair_Muhammad's solution, it is an easy solution.

You could refer to below link to add index:

https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi

Regards,

Daniel He

[Anonymous]

Hi @v-danhe-msft,

This is for the monthly report, data has around 40K lines, if I will reduce the number of lines then the formula is working. 

Otherwise, it shows an error "There's not enough memory to complete this operation. Please try again later when there may be more memory available."

Thanks

Ket

[v-danhe-msft · Accepted solution]

Hi @Anonymous,

If you do need to consider about the performence, I suggest you use a measure instead of the calculated column:

Measure = 
IF (
    COUNTROWS (
        FILTER (
            ALL(Table1),
            [Index] < MAX( [Index] )
                && [Item] = MAX(Table1[Item] )
        )
    )
        > 0,
    0,
    1
)

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He

[Zubair_Muhammad]

@Anonymous

In PowerBI, you will need little support from Index Column..then you can use this calculated column

Calculated Column =
IF (
    COUNTROWS (
        FILTER (
            Table1,
            [Index] < EARLIER ( [Index] )
                && [ColumnName] = EARLIER ( [ColumnName] )
        )
    )
        > 0,
    0,
    1
)

[Anonymous]

hey @Zubair_Muhammad,

I tried it but it shows an error "There's not enough memory to complete this operation. Please try again later when there may be more memory available."

can you suggest something?

Thanks