Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
SandeA
Helper III
Helper III

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 need to have something to show them by the end of the week.

 

I have two issues that I just can't figure out.

 

ISSUE 1:

I created a table to only have specific columns from an imported sql table. This is so I can use this in an EXCEPT table later:

 RStars Travel = SELECTCOLUMNS(RStarsTravelReport,
"VPT #", RStarsTravelReport[RSTravel_VPTNbr],
"Date",RStarsTravelReport[Final_Approv_Dt],
"Employee Name",RStarsTravelReport[Vendor_Name], "Amount",IF(RStarsTravelReport[Sfms_Trans_Amt]>0,RStarsTravelReport[Sfms_Trans_Amt],0))
 
Here is a sample of that table:
SandeA_0-1624466748930.png

The other table (Travel Claims Log) I used in the EXCEPT table has this data: (note: 61853 is the first record and lowest number in this table)

SandeA_1-1624466857905.png

 

Then I created the EXCEPT table:
OutstandingTravel = EXCEPT('Travel Claims Log','RStars Travel')
 
This is a sample of that table:
 SandeA_2-1624466975455.png

 

The table genrated just fine, however it pulled in rows that were in both of the tables. I tried switching the order of the table and that definitely isn't what I'm looking for! I need to see all records that are in the Travel Claims Log but NOT in the RStars Travel. While it did pull the first record correctly, it should not have pulled the remaining items because those are in both tables. Is it because the Travel Claims has each VPT # summarized and the RStars Travel does not? If so, is there a way around that?
 
ISSUE 2
Some of the VPT # records in the RStars Travel table begin with a 'C' or 'LT'. We do not want to include these rows in any of our dashboard visuals or counts. Is there a way to exclude those when I'm creating the table? I couldn't find a function or operator that was for 'does not include' that would work in this situation. 
 
Thank you for any help you can give, you all are GREAT!!!
Sande

 

1 ACCEPTED SOLUTION
CNENFRNL
Community Champion
Community Champion

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.


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

2 REPLIES 2
CNENFRNL
Community Champion
Community Champion

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.


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Fowmy
Super User
Super User

@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?

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Find out what's new and trending in the Fabric Community.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.