Forum Discussion

SandeA's avatar
SandeA
Helper III
5 years ago
Solved

Excluding specific rows based on content

I'm sorry in advance if there is an easy solution that I'm just not finding. I'm working on a very high priority Dashboard project and have spent 2 days trying to figure out how to make this work. I ...
  • CNENFRNL's avatar
    5 years ago

    Issue 1:

    EXCEPT ( <LeftTable>, <RightTable> )

    it excludes from LeftTable the rows also existing in RightTable; which means all columns of a row, i.e. VPT, DATE, EMP NAME and AMOUNT in your case, are taken into consideration. At a glimpse, for row 'Travel Claims Log'[VPT #]=61854, 'Travel Claims Log'[AMOUNT]=477.45 doesn't exist in 'RStars Travel', thus this row is kept.

     

    Issue 2,

    RStars Travel =
    SELECTCOLUMNS(
        FILTER(
            RStarsTravelReport,
            NOT (
                LEFT( RStarsTravelReport[RSTravel_VPTNbr] ) = "C"
                    || LEFT( RStarsTravelReport[RSTravel_VPTNbr], 2 ) = "LT"
            )
        ),
        "VPT #", RStarsTravelReport[RSTravel_VPTNbr],
        "Date", RStarsTravelReport[Final_Approv_Dt],
        "Employee Name", RStarsTravelReport[Vendor_Name],
        "Amount", MAX( RStarsTravelReport[Sfms_Trans_Amt], 0 )
    )
    

    filter out undesired rows with specific symbols upon creating the new table.