Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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:
You could also download the pbix file to have a view.
Regards,
Daniel He
Hi @Ket,
Could you please offer some sample data and post your desired result if possible?
Regard,
Daniel He
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
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
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:
You could also download the pbix file to have a view.
Regards,
Daniel He
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 )
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
97 | |
79 | |
66 | |
62 |
User | Count |
---|---|
145 | |
113 | |
105 | |
85 | |
65 |