topic Re: Filtering duplicate entries from a column in a measure in DAX Commands and Tips

Filtering duplicate entries from a column in a measure

jackb77 — Mon, 05 Jun 2023 07:16:30 GMT

Re: Filtering duplicate entries from a column in a measure

jackb77 — Mon, 05 Jun 2023 07:22:22 GMT

At my workplace, I have a column that shows an employee name [employee_name] with multiple distinct, non-unique string values pertaining to a unique id, i.e. one activity can have multiple "employees" working on it at different times.

I need a measure which counts the unique number of id’s with only this particular string value (“Ikke tildelt”) but not id’s that contains another value from a calculated colum (Team 1), so basically ID's which have been assigned to a team, but have this specific employee value ("Ikke tildelt"), and no other employee values yet. FYI there is no way to rank these activities chronologically.

The current solution, which I borrowed from a different post, works in that it shows the count of values correctly, but it is a) very slow b) I cannot drillthrough (out of memory) when a table has 2 mil+ rows.

Any ideas, please? I am at my wits end.

Currently I use this measure:

xxx, ikke tildelt =

VAR Tildelte = CALCULATETABLE(DISTINCT('table_1'[UniqueId]), 'table_1' [employee_name] <> "Ikke tildelt" && 'table_1' [Team] = "Team 1")

VAR Result =

CALCULATE (

COUNTROWS ( DISTINCT ( ('table_1'[UniqueId]) ) ),

'table_1' [employee_name] = "Ikke tildelt" && 'table_1' [Team] = "Team 1",

NOT 'table_1'[UniqueId] IN Tildelte

)

RETURN Result

Re: Filtering duplicate entries from a column in a measure

tamerj1 — Mon, 05 Jun 2023 08:26:11 GMT

Hi jackb77
Please try

xxx, ikke tildelt = COUNTROWS ( CALCULATETABLE ( DISTINCT ( 'table_1'[UniqueId] ), FILTER ( 'table_1', 'table_1'[employee_name] <> "Ikke tildelt" && 'table_1'[Team] = "Team 1" ) ) )

Re: Filtering duplicate entries from a column in a measure

jackb77 — Mon, 05 Jun 2023 09:33:14 GMT

Hi tamerj1

Thank you for trying.

The solution as proposed, results in showing the id's which doesn't have the value "ikke tildelt" in [employee_name]. = "ikke tildelt" result in all id's which have the value "tildelt", not those which only have the value tildelt.

Re: Filtering duplicate entries from a column in a measure

tamerj1 — Mon, 05 Jun 2023 10:56:18 GMT

jackb77
Please try

ikke tildelt = VAR T1 = FILTER ( ALLSELECTED ( 'table_1' ), 'table_1'[Team] = "Team 1" ) VAR T2 = FILTER ( T1, 'table_1'[employee_name] <> "Ikke tildelt" ) VAR Tildelte = DISTINCT ( SELECTCOLUMNS ( T2, "@ID", 'table_1'[UniqueId] ) ) VAR T3 = FILTER ( T1, 'table_1'[employee_name] = "Ikke tildelt" && NOT 'table_1'[UniqueId] IN Tildelte ) VAR Result = COUNTROWS ( DISTINCT ( SELECTCOLUMNS ( T3, "@ID", 'table_1'[UniqueId] ) ) ) RETURN Result

Re: Filtering duplicate entries from a column in a measure

jackb77 — Mon, 05 Jun 2023 13:35:09 GMT

Counts the number correctly, but filters don't apply in drillthrough, just lists all id's (apparently, haven't actually counted) no matter the filters i apply

Re: Filtering duplicate entries from a column in a measure

tamerj1 — Mon, 05 Jun 2023 14:47:04 GMT

jackb77

Would you please provide more details about your visual and which columns are involved in it and in filtering?

Re: Filtering duplicate entries from a column in a measure

jackb77 — Fri, 23 Jun 2023 06:44:55 GMT

Due to the work, I cannot give more details at present.

I have found a solution, which is inelegant but nonetheless works, though in the most roundabout way (creating calculated columns for each variable i want to filter by).

Thank you for your help.

Re: Filtering duplicate entries from a column in a measure

devanshi — Fri, 23 Jun 2023 07:04:53 GMT

Duplicate count = DISTINCTCOUNT('Table'[ID])

Re: Filtering duplicate entries from a column in a measure

jackb77 — Fri, 23 Jun 2023 08:18:58 GMT

So here's the tedious workaround (which my coworker did) for the calculated column(s). There's one for the opposite variable "tildelt" as well of course:

Ikke tildelt =

VAR currentRow = 'table1'[uniqueId]

VAR antal_linjer =

CALCULATE (

COUNT ( 'table1'[employeename] ),

FILTER (

'table1',

'table1'[employeename] = "Ikke tildelt"

&& 'table1'[uniqueId] = currentRow

)

RETURN

IF ( antal_linjer >= 1, 1, 0 )