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
SSJ011
Frequent Visitor

Return a one for the first instance of duplicate values and a “zero” for the other instances

Hi All, 

 

Need to calculate and return as 1 for the first occurrence of the value and 0 for the other occurrences as shown in the below table.

 

Telex NoCount
43833.2502893519FR3186011
43833.9684606481ER257251
44104.1667708333FR3859981
44104.6828935185FR3861641
44104.5247916667FR3861221
44104.5247916667FR3861220
44104.5247916667FR3861220
44104.5247916667FR3861220
44104.5247916667FR3861220
44104.6828935185FR3861640
44104.6828935185FR3861640
44104.5247916667FR3861220
44104.6828935185FR3861640
44104.6830092593FR3861711
44104.7237847222FR3857341
44104.7615625FR3860171
44104.6830092593FR3861710
44104.7143518519FR3862001
44104.7160300926FR3862051
44104.7160300926FR3862050
44104.7160300926FR3862050
44104.7206134259FR3855041
44104.7237847222FR3857340
44104.6830092593FR3861710
44104.7237847222FR3857340
44104.7237847222FR3857340
44104.6830092593FR3861710

 

Thanks in advance for your assistance.

 

Regards
Sachin 

5 REPLIES 5
amitchandak
Super User
Super User

@SSJ011 , first all you have add an index column

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

 

Then create a new column like this in dax

if([Index] = minx(filter(Table, [Telex No] = earlier([Telex No])),[Index]), 1, 0)

daxer-almighty
Solution Sage
Solution Sage

@SSJ011,

 

This is a job for Power Query, not for DAX. Please use the right tool for the job unless you want to suffer later on.

CNENFRNL
Community Champion
Community Champion

Hi, @SSJ011 , it requires indexing the table to differentiate rows; then you might apply this measure to a calculated column,

 

Count = 
( Logs[Index]
    = MINX (
            FILTER ( Logs, Logs[Telex No] = EARLIER ( Logs[Telex No] ) ),
            Logs[Index]
        )
) + 0

 

Screenshot 2020-11-22 115335.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Can we acheive the same output using a "count" measure, instead of a calculated column?

wdx223_Daniel
Super User
Super User

@SSJ011 DAX can not identify which one is the first if only have one column as you show. you need other columns which can make each row is unique.

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.

Top Solution Authors