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
alejandrofm
Helper II
Helper II

Logical operations between two tables

Hi! I have 2 tables:

Table A: document | start date | end date | Yearmonth

Table B: document | date | Yearmonth

 

Table A had duplicates so I created a referenced table to table A grouping by document, min(start), max(end), this way I now have 1 document per row, and I created a relation between this and B on document.

 

Not every field in B has a document in A.

 

I need to know how many of the records in B have a date between A[start date] and A[start date].

 

I tryied a lot of things but the simplest of all I can't directly compare values using IF.

Any ideas?

Thank you!

1 ACCEPTED SOLUTION
alejandrofm
Helper II
Helper II

Ok, I solved like this:

New column-> Time_ok = IF ( 'Table B'[date]< RELATED ( 'Table A'[max_date] ) ) ;1;0 )

 

And new measure to get the percentages:

 

New Measure -> % tiempo = DIVIDE(SUM('Table B'[Time_ok]);count('Table B'[document])) 

 

It seems to work, buy I'm new to Power BI so I'd like to know if there is a better solution.

Thank you!

View solution in original post

3 REPLIES 3
alejandrofm
Helper II
Helper II

Ok, I solved like this:

New column-> Time_ok = IF ( 'Table B'[date]< RELATED ( 'Table A'[max_date] ) ) ;1;0 )

 

And new measure to get the percentages:

 

New Measure -> % tiempo = DIVIDE(SUM('Table B'[Time_ok]);count('Table B'[document])) 

 

It seems to work, buy I'm new to Power BI so I'd like to know if there is a better solution.

Thank you!

@alejandrofm,

 

You may also try SUMX Function to add a measure directly.

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

Great! but I think will lost readeability if I continue to nest functions (because I'm new and want to understand my code in 2 weeks, when I advance furter I'll begin using your method), thanks!

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.