Logical operations between two tables

[Anonymous]

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!

[Anonymous · Accepted solution]

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!

[v-chuncz-msft]

@Anonymous,

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

[Anonymous]

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!