topic Counting only unique rows in DAX Commands and Tips

Counting only unique rows

stribor45 — Fri, 09 Aug 2024 15:08:44 GMT

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

Re: Counting only unique rows

Greg_Deckler — Fri, 09 Aug 2024 15:52:01 GMT

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

Re: Counting only unique rows

stribor45 — Sat, 10 Aug 2024 02:38:10 GMT

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.

Re: Counting only unique rows

Greg_Deckler — Sat, 10 Aug 2024 20:58:31 GMT

stribor45 DISTINCT has a table version as well: DISTINCT (table) function (DAX) - DAX | Microsoft Learn