Forum Discussion

stribor45's avatar
stribor45
Post Prodigy
2 years ago
Solved

Counting only unique rows

Can this code be improved to count unique rows based on column A in MY_DATA table?   DEFINE VAR B = COUNTROWS (FILTER ( 'MY_DATA', 'MY_DATA'[start tim...
Help
  • Greg_Deckler's avatar
    Greg_Deckler
    2 years ago

    stribor45 So like this?

    DEFINE

VAR B = 

COUNTROWS( DISTINCT( SELECTCOLUMNS(FILTER 
            (
               'MY_DATA',
               'MY_DATA'[start time] IN DATESBETWEEN(
                                       'My_DATA'[start time], 
                                        DATE(2024, 7, 1), DATE(2024, 7, 31)
                                      )
           ), "__Column", [A] ) )
)
     
EVALUATE

{{B}}
Greg_Deckler's avatar
Greg_Deckler
Community Champion
2 years ago

stribor45 So like this?

DEFINE

VAR B = 

COUNTROWS( DISTINCT( SELECTCOLUMNS(FILTER 
            (
               'MY_DATA',
               'MY_DATA'[start time] IN DATESBETWEEN(
                                       'My_DATA'[start time], 
                                        DATE(2024, 7, 1), DATE(2024, 7, 31)
                                      )
           ), "__Column", [A] ) )
)
     
EVALUATE

{{B}}
  • stribor45's avatar
    stribor45
    Post Prodigy
    2 years ago

     I need to adjust the code but I am confused as a distinct function only takes one column table as an argument.  This is the code 

    EVALUATE
SELECTCOLUMNS (
                    FILTER (
                        'MY_DATA',
                        'MY_DATA'[start time]
                            IN DATESBETWEEN (
                                'MY_DATA'[start time],
                                DATE ( 2024, 5, 1 ),
                                DATE ( 2024, 5, 31 )
                            )
                    ),
                    "__Columns", [call duration],
                    "__Column", [id] 
)

    Code above produces this table as a result (see the image below). Is there a way to remove duplicates row based on the [id]? Resulting table in the image produces duplicates and I need to end up with only two rows.