Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Ket
Frequent Visitor

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

1 ACCEPTED SOLUTION

Hi @Ket,

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:

1.PNG

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

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
v-danhe-msft
Employee
Employee

Hi @Ket,

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

 

Regard,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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   

 

Capture2.PNG

Hi @Ket,

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

 

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Hi @Ket,

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:

1.PNG

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

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Zubair_Muhammad
Community Champion
Community Champion

@Ket

 

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
)

Regards
Zubair

Please try my custom visuals

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.