Forum Discussion
Flagging rows with collections values across multiple rows with duplicates
Hello, When the: account number, visit date, and departments are the same in a transaction table, our practice management system is incorrectly flagging rows for exclusion with "Y/N". In th...
FreemanZ
Super User
Super Userhi Anonymous
not sure if i fully get you, try to add a column like:
Column =
VAR _table =
FILTER(
SELECTCOLUMNS(
TableName,
"Account", TableName[Account],
"Visit Date", TableName[Visit Date],
"Dept", TableName[Dept]
),
TableName[Account]=EARLIER(TableName[Account])
&&TableName[Visit Date]=EARLIER(TableName[Visit Date])
&&TableName[Dept]=EARLIER(TableName[Dept])
)
VAR condition1 = COUNTROWS(_table)
VAR condition2 =
COUNTROWS(
DISTINCT(
FILTER(
_table,
TableName[Include]=EARLIER(TableName[Include])
)
)
)
RETURN
SWITCH(
TRUE(),
condition1=2&&condition2=2&&TableName[Include]="N", "Y",
condition1=2&&condition2=2&&TableName[Include]="Y", "N",
[Include]
)
it worked like:
I added this DAX which churned but I ended up maxing out my memory. I'll keep trying, maybe on a different machine.
I realized that in the last table example, misstated what the result should be, apologies. The second table example is the desired result:
Account Visit Date Dept Include Payment Due Corrected 12345A 1/23/2023 Diagnostic N 280 345 Y 12345A 1/23/2023 Diagnostic Y 0 345 N When the criteria are not met, I just need the current [Include] value returned
table results
Account Visit Date Dept Include Payment Due Corrected 12345A 1/23/2023 Diagnostic N 280 345 Y 12345A 1/23/2023 Diagnostic Y 0 345 N 12345A 1/30/2023 Diagnostic Y 25 65 Y 23569B 1/31/2023 Other Y 40 0 Y I hope this clarifies.
Thanks,
Chase