Forum Discussion

liselotte's avatar
liselotte
Icon for Advocate I rankAdvocate I
2 years ago
Solved

Flag duplicates in var table DAX

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.

Need Help
  • AmiraBedh's avatar
    AmiraBedh
    2 years ago

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

     

     

     

3 Replies

  • AmiraBedh's avatar
    AmiraBedh
    Icon for Super User rankSuper User
    2 years ago

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