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 time] IN DATESBETWEEN(
                                       'My_DATA'[start time], 
                                        DATE(2024, 7, 1), DATE(2024, 7, 31)
                                      )
           )
)
     
EVALUATE

{{B}}
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}}

3 Replies

  • 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.