UNIQUE VALUE Checker in PowerPivot

[Jakub]

Hey Guys,


Im struggling to figure out a formula that could help me identify the first unique value in a column.

So for example, for names of fruits I would get


Apple 1
Apple 0
Orange 1
Lemon 1
Orange 0


1 - first unique value
0 - if duplicate


Thanks !

Jakub

 

[olivierhbh]

Hello Jakub,

There is no row order in PP, thus you can't really detect if it's the first time you see an occurrence or not.

To do that you would need to add an index column like that:

[TABLE="width: 128"]
<tbody>[TR]
[TD="width: 64"]index[/TD]
[TD="width: 64"]fruits[/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD="class: xl65"]3[/TD]
[TD]Orange[/TD]
[/TR]
[TR]
[TD="class: xl65"]4[/TD]
[TD]Lemon[/TD]
[/TR]
[TR]
[TD="class: xl65"]5[/TD]
[TD]Orange[/TD]
[/TR]
[TR]
[TD="class: xl65"]6[/TD]
[TD]Apple[/TD]
[/TR]
</tbody>[/TABLE]

Then you would be able to do what you want based on the index order with this formula:

=IF (
    CALCULATE (
        MIN ( [index] ),
        FILTER ( ALL ( Table1), Table1[fruits] = EARLIER ( Table1[fruits] ) )
    )
        = [index],
    1,
    0
)

Let me know if it helps,
Olivier.

 

[Jakub]

Yes it did !! Thanks a lot