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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Index duplicates then order by alphabetical order

I created a table using the 'earlier' dax function, but I am now having problems dealing with the duplicates.

The tables below would illustrate.

Activity ID Department Date Count
1203993 Young 15-03-2021 20
1394994 Young 15-03-2021 20
1475478 Young 15-03-2021 20
1458990 Young 15-03-2021 20
1457373 Young 16-03-2021 16
1243536 Young 16-03-2021 16
5647747 Ran 13-03-2021 14
5657757 Ran 13-03-2021 14
7907000 Ran 15-03-2021 12
5768688 Ran 15-03-2021 12
5767678 Ran 15-03-2021 12

 

I am thinking the way to do this would be to create a new index column that gives unique indexes based on filtering criteria as below:

Activity ID Department Date Count Index
1203993 Young 15-03-2021 20 1
1394994 Young 15-03-2021 20 2
1475478 Young 15-03-2021 20 3
1458990 Young 15-03-2021 20 4
1457373 Young 16-03-2021 16 1
1243536 Young 16-03-2021 16 2
5647747 Ran 13-03-2021 14 1
5657757 Ran 13-03-2021 14 2
7907000 Ran 15-03-2021 12

 

1

5768688 Ran 15-03-2021 12 2
5767678 Ran 15-03-2021 12 3

 

The index would be based on the Activity ID (which are string elements). So invariably, the table is filtered for Department and Dates then an index is generated based on the alphabetical ordering of the Activity ID. I can then filter out the duplicates by selecting the Index 1 as below.

 

Activity ID Department Date Count Index
1203993 Young 15-03-2021 20 1
1457373 Young 16-03-2021 16 1
5647747 Ran 13-03-2021 14 1
7907000 Ran 15-03-2021 12

 

1

 

I have tried using rank and calculate, but  generated error. I would appreciate if someone can advice what to do...or maybe an easier way to deal with duplicates after using the earlier function.

0 REPLIES 0

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

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

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors