topic How to assess a row based in two dates from another table? in DAX Commands and Tips

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

luisbaleeiro — Sun, 15 Sep 2024 19:01:12 GMT

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

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

SachinNandanwar — Sun, 15 Sep 2024 22:44:45 GMT

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

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

powerbiexpert22 — Mon, 16 Sep 2024 00:32:31 GMT

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

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

Anonymous — Tue, 17 Sep 2024 08:30:24 GMT

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.