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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Compare all values in column from one table with single value from other table

Hi, 

 

I've the next doubt: suppose that I have these tables, Dates and Projects:

 

Dates

Date

1981-01-01

1992-02-02

1993-03-03

2018-04-04

2019-04-04

 

Projects

Identifier     Name      Creation_date

ID1              Name1    1992-09-09

ID2              Name2    2018-02-02

 

What I want to get is the number of dates from Dates that are previous to Creation_date in Projects. In this example, for ID1 the value would be 2 (1981-01-01 and 1992-02-02) and for ID2 the value would be 3 (1981-01-01, 1992-02-02 and 1993-03-03). How can I achieve this?

 

Thank you for your help 🙂

 

4 REPLIES 4
PattemManohar
Community Champion
Community Champion

@Anonymous  Please add a New Column in Projects Table as below

 

Cnt = COUNTROWS(FILTER(Test305Dates,[Date]<Test305Projects[CreationDate]))

image.png





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




Anonymous
Not applicable

Hi @PattemManohar . First of all, thanks for your early response. I have a doubt related to Date field. How can I invoke it? Shoud I invoke it as Dates[Date]? In that case, PowerBI alerts me that a single value for Dates[Date] cannot  be determined. What's wrong with this?

hi, @Anonymous 

If you could just use this formula to add a new column

Result = COUNTROWS(FILTER(Dates,Dates[Date]<Projects[Creation_date]))

If not your case, please share your sample pbix file.

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@Anonymous  Could you please post the screenshot of the error message. Yes, it is Dates[Date]. I just referenced as [Date] as it is within the same Filter Context.





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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