Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
Solved! Go to 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()
)
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()
)
Thank you a lot! It works like a charm!
Glad to help 😄
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
96 | |
92 | |
86 | |
69 |
User | Count |
---|---|
161 | |
126 | |
119 | |
109 | |
95 |