How to assess a row based in two dates from another table?

[luisbaleeiro]

Hi, guys

I would like to assess a date (table1) based in two diferent dates from another table (table2). It must be done for each raw from table2. 

If at leats returns one true situation, returns 1. If not, returns 0.

Example: jose has one result as 1, because there is at least one raw in a table2 where the date is between the date1 and the date2. In this case, 5-jan is gratter than 1-jan anda smaller than 15-jan

table1			table2
nome	data	result		nome	data1	data2
jose	5-jan	1		jose	1-jan	15-jan
jose	20-jan	0		jose	1-jan	15-jan
maria	5-jan	1		jose	1-feb	15-feb
maria	20-jan	0		maria	1-jan	15-jan
	total	2		maria	1-jan	15-jan
				maria	1-feb	15-feb

[powerbiexpert22 · Accepted solution]

Hi @luisbaleeiro ,

please follow below steps , attached is sample report

step1: add index column in both table

step2: create calculated column as shown below 

https://drive.google.com/file/d/1A5fy8I-2neLfSALj_f87q63HTVe3PJMY/view?usp=drive_link

[Anonymous · Accepted solution]

Hi @luisbaleeiro 

Thank you very much powerbiexpert22 and SachinNandanwar for your prompt reply.

For your question, here is the method I provided:

Create a new column.

Result = 
IF (
    COUNTROWS (
        FILTER (
            'Table 2',
            'Table 2'[nome] = 'Table 1'[nome] &&
            'Table 1'[data] >= 'Table 2'[data1] &&
            'Table 1'[data] <= 'Table 2'[data2]
        )
    ) > 0,
    1,
    0
)

Here is the result.

Regards,

Nono Chen

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

[powerbiexpert22 · Accepted solution]

Hi @luisbaleeiro ,

please follow below steps , attached is sample report

step1: add index column in both table

step2: create calculated column as shown below 

https://drive.google.com/file/d/1A5fy8I-2neLfSALj_f87q63HTVe3PJMY/view?usp=drive_link

[SachinNandanwar]

Can you please format the sample data that you posted in a more readable manner ?