Forum Discussion
Excluding specific rows based on content
- 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.
SandeA
When you use EXCEPT, it matched columns from both tables by positions, and columns are compared
"If a row appears at all in both tables, it and its duplicates are not present in the result set. If a row appears in only table_expression1, it and its duplicates will appear in the result set."
Regarding the Issues # 2 yes it is possible.
Can you share a PBIX file with sample data with the scenario and the expected results?