cancel
Showing results for 
Search instead for 
Did you mean: 
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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors