Forum Discussion
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!
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!
3 Replies
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!
