DAX measure to count duplicates?

user_13747481 0
Viewed 1502

I'm looking for some guidance on how to structure a DAX measure that will count whether a row is a duplicate.

To be counted as a duplicate, the combination of the two columns named: Latitude & Longitude need to be identical. The customers are distinct here.

How would I create a DAX measure called occurrence in Power BI to capture that?

Customer Latitude Longitude Occurance
C1 25.2318994 89.0194663 3
C2 25.2318994 89.0194663 3
C3 22.8698924 89.0379756 2
C4 25.2318994 89.0194663 3
C5 22.8698924 89.0379756 2
C6 23.2740535 88.0194665 1
dax powerbi data-visualization business-intelligence
3 Answers

Maybe with ALLEXCEPT:

Occurrence = CALCULATE(COUNTROWS(Table), ALLEXCEPT(Table, Table[Lat], Table[Lon]))
user_13090270

You can try this below measure-

Occurrence = 

var curent_row_lat = min(your_table_name_5[Latitude])
var curent_row_lon = min(your_table_name_5[Longitude])

RETURN 
COUNTROWS(
    FILTER(
        ALL(your_table_name_5),
        your_table_name_5[Latitude] = curent_row_lat
            && your_table_name_5[Longitude] = curent_row_lon
    )
)

Output-

enter image description here

user_36523450 
Occurence = 
    RANKX(
        FILTER(
                'Table',
                'Table'[Latitude]=EARLIER('Table'[Latitude]) &&  'Table'[Longitude]=EARLIER('Table'[Longitude])
                ),
        'Table'[Customer], , DESC
        )
user_95177690
Related