Forum Discussion

pmadam's avatar
pmadam
Icon for Helper II rankHelper II
4 years ago
Solved

Apply filter in Union Table

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's avatar
    Anonymous
    4 years ago

    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.

2 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.