Flag duplicates in var table DAX

[liselotte]

I have table ValueTable. I create a var table of ValueTable filtered by a date in Slicer, then I flag the duplicates of IDs with this query:

Flag =
var _table = ADDCOLUMNS(SUMMARIZE(
FILTER('fact_TimeTable',
('fact_ValueTable'[Date].[Date]=MAX('DateSlicer'[SelectedDate])) &&
('fact_ValueTable'[IsActive]=1)
)
, [ID]
, [Value]
)
, "IDx", [ID])
return
IF(CALCULATE(COUNTROWS(_table), ALLEXCEPT(_table, [IDx]))>1,TRUE(),FALSE())

 But I get an error of Cannot find name ID. Does anyone know why? Thank you in advance.

[AmiraBedh · Accepted solution]

You're trying to flag rows where the ID appears more than once on the same date and when IsActive is 1, based on a selected date from DateSlicer.

Try the following :

Flag =
VAR SelectedDate = MAX('DateSlicer'[SelectedDate])
VAR IsActiveFilter = 1
VAR _table =
    FILTER(
        ALL('fact_ValueTable'), -- Consider using ALL to remove filters if necessary
        'fact_ValueTable'[Date] = SelectedDate &&
        'fact_ValueTable'[IsActive] = IsActiveFilter
    )
VAR _summarizedTable =
    SUMMARIZE(
        _table,
        'fact_ValueTable'[ID],
        "Count", COUNT('fact_ValueTable'[ID]) -- This adds a count of each ID
    )
RETURN
    IF(
        LOOKUPVALUE(
            _summarizedTable[Count],
            _summarizedTable[ID], EARLIER('fact_ValueTable'[ID])
        ) > 1,
        TRUE(),
        FALSE()
    )

[liselotte]

Thank you a lot! It works like a charm!

[AmiraBedh]

Glad to help 😄