Flagging rows with collections values across multiple rows with duplicates

Question

Hello,&nbsp;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".&nbsp;&nbsp;&nbsp;In the example below, the Y/N column should be reversed:&nbsp;AccountVisit DateDeptIncludePaymentDue12345A1/23/2023DiagnosticN28034512345A1/23/2023DiagnosticY0345&nbsp;&nbsp;What I need is an additional column that has a Corrected flag column that flags a "Y" for the row with a Payment and a "N" for the row w/o (see far right column below):&nbsp;AccountVisit DateDeptIncludePaymentDueCorrected12345A1/23/2023DiagnosticN280345Y12345A1/23/2023DiagnosticY0345N&nbsp;Since this scenario only occurs when the account/visit date/dept are the same down columns the solution should simply carry over the value in the include column when the criteria described above are not met.&nbsp; When the value for the Include column are both N or both Y, I would need those values carried over in to the correct column as below:&nbsp;AccountVisit DateDeptIncludePaymentDueCorrected12345A1/23/2023DiagnosticN280345N12345A1/23/2023DiagnosticN0345N12345A1/31/2023DiagnosticY280345YAny help greatly appreciated!&nbsp;Thank you!

freemanz · Answer

hi&nbsp;Anonymous&nbsp;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])
        &amp;&amp;TableName[Visit Date]=EARLIER(TableName[Visit Date])
        &amp;&amp;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&amp;&amp;condition2=2&amp;&amp;TableName[Include]="N", "Y", 
    condition1=2&amp;&amp;condition2=2&amp;&amp;TableName[Include]="Y", "N", 
    [Include]
)&nbsp;it worked like:&nbsp;

anonymous · Answer

FreemanZ&nbsp;&nbsp;I added this DAX which churned but I ended up maxing out my memory.&nbsp; I'll keep trying, maybe on a different machine.&nbsp;I realized that in the last table example, misstated what the result should be, apologies. The second table example is the desired result:AccountVisit DateDeptIncludePaymentDueCorrected12345A1/23/2023DiagnosticN280345Y12345A1/23/2023DiagnosticY0345N&nbsp;When the criteria are not met, I just need the current [Include] value returned&nbsp;table resultsAccountVisit DateDeptIncludePaymentDueCorrected12345A1/23/2023DiagnosticN280345Y12345A1/23/2023DiagnosticY0345N12345A1/30/2023DiagnosticY2565Y23569B1/31/2023OtherY400Y&nbsp;&nbsp;I hope this clarifies.&nbsp;Thanks,&nbsp;Chase

Forum Discussion

Flagging rows with collections values across multiple rows with duplicates

2 Replies

Related content

Flag duplicates with DAX

Duplicate ranking

Notebook Error: CANNOT_OPEN_SOCKET in collect()

How to flag duplicates with DAX?

remove duplicate value within a row

Account	Visit Date	Dept	Include	Payment	Due
12345A	1/23/2023	Diagnostic	N	280	345
12345A	1/23/2023	Diagnostic	Y	0	345