Forum Discussion
liselotte
Advocate I
Advocate IFlag 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_T...
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() )
AmiraBedh
Super User
Super UserYou'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()
)
