Apply filter in Union Table

[pmadam]

Hi Team

Is it possible for me to remove rows from a union table created in Dax based on the value of a column?  Need to remove so table has unique values so I can use the Related function.  Currently many to many.

From 'UNIONTABLE-BKM-NDC' I think I want to remove all rows where the value in column 'APPR DATE CHECK' is "NOT OLDEST DATE'. I think this should leave me with only unique values.... 

_APPR DATE CHECK = IF('UNIONTABLE-BKM-NDC'[AddedDate]= CALCULATE(MIN('UNIONTABLE-BKM-NDC'[AddedDate]),ALLEXCEPT('UNIONTABLE-BKM-NDC','UNIONTABLE-BKM-NDC'[OrderNumber])),"OLDEST DATE","NOT OLDEST DATE")

[Anonymous · Accepted solution]

Hi @pmadam ,

According to your statement, I think "UNIONTABLE-BKM-NDC" is an UNION table by dax. [_APPR DATE CHECK] should be a calculated column to return flag based on some conditions.

You can create a virtual table and filter it.

UNIONTABLE-BKM-NDC =
VAR _BASIC_UNION =
    UNION ( 'Table1', 'Table2' )
VAR _ADD_APPR_DATE_CHECK =
    ADDCOLUMNS (
        _BASIC_UNION,
        "_APPR DATE CHECK",
            IF (
                [AddedDate]
                    = MINX (
                        FILTER ( _BASIC_UNION, [OrderNumber] = EARLIER ( [OrderNumber] ) ),
                        [AddedDate]
                    ),
                "OLDEST DATE",
                "NOT OLDEST DATE"
            )
    )
VAR _FILTER =
    FILTER ( _ADD_APPR_DATE_CHECK, [_APPR DATE CHECK] <> "NOT OLDEST DATE" )
RETURN
    _FILTER

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tamerj1]

@pmadam 
This should work for a calculated column. What results are you getting?