cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
jamjoom
New Member

Filtering column based on another table | power query

Hi All,

 

I have the following tables:

 

Employee

NameDate of Birth
Andrew5/13/1998
Michael9/3/1990

 

SalesByCity

CitySalesDate
Orlando2,000,0005/14/2001
Scottsdale6,000,0006/15/1980
Tampa1,000,0006/19/2005

 

And would love to have this table:

RESULT

CitySalesDate
Orlando2,000,0005/14/2001
Tampa1,000,0006/19/2005

 

The "Result" table was generated based on the max date from table "Employee". 

 

I can come up with the same result using this function: 

= Table.SelectRows(#"previous step", each [ValueDate] < #date(1998, 5, 13))

 

Is there a way to have this formula but not using date numbers; meaning, can I change this part "#date(1998, 5, 13))" to a formula that gives my result in the following way:

1) Check max date in Table "Employee"

2) Filter Table 2 to only show anything after the Max Date?

 

I hope this was clear enough. Thank you in advance.

1 ACCEPTED SOLUTION
wdx223_Daniel
Super User
Super User

=let a=List.Max(Employee[Date of Birth]) in Table.SelectRows(PreviousStepName,each [ValueDate]>a)

View solution in original post

2 REPLIES 2
wdx223_Daniel
Super User
Super User

=let a=List.Max(Employee[Date of Birth]) in Table.SelectRows(PreviousStepName,each [ValueDate]>a)

thank you so much! 

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors