Filtering duplicate entries from a column in a measure

[devanshi]

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

[tamerj1]

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"
        )
    )
)

[jackb77]

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.

[jackb77]

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 )

[jackb77]

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.

[tamerj1]

@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

[jackb77]

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

[tamerj1]

@jackb77 

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

[jackb77]

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