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
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.
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.
User | Count |
---|---|
37 | |
36 | |
30 | |
29 | |
21 |
User | Count |
---|---|
61 | |
41 | |
32 | |
18 | |
16 |