Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Find Unique values in column

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

Need Help
  • v-danhe-msft's avatar
    v-danhe-msft
    7 years ago

    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

7 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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

  • v-danhe-msft's avatar
    v-danhe-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi Anonymous,

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

     

    Regard,

    Daniel He

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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