cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ayrtonsantos Frequent Visitor
Frequent Visitor

if and statement on unrelated tables

Hi everyone, I'm a kind of new using Power BI, I've been using for the last 1 year and I love this forum, most of my expertise I learned here.

It's my first time creating a question, because this one I didn't find a solution, If there is a topic of someone talking about the same thing, please share the link I will be happy to find a solution to my issue there.

 

Here it comes:

 

I have two tables (unrelated tables), there is no relationship between them.

 

The first one we can consider as a transactions table, there is a timestamp of a transaction and one column that counts a item.

 

 DateTime   -  Cars

01/08/2018 07:55:00

1

01/08/2018 08:00:01

1

01/08/2018 10:16:02

1

01/08/2018 13:30:03

1

03/08/2018 00:00:04

1

03/08/2018 14:05:05

1

03/08/2018 00:00:06

1

03/08/2018 04:00:14

1

05/08/2018 00:00:15

1

05/08/2018 00:00:16

1

07/08/2018 00:00:17

1

07/08/2018 14:04:58

1

10/08/2018 12:04:59

1

14/08/2018 00:00:20

1

14/08/2018 00:00:21

1

14/08/2018 00:00:22

1

17/08/2018 15:04:10

1

17/08/2018 15:04:11

1

17/08/2018 15:04:12

1

17/08/2018 13:04:13

1

17/08/2018 13:04:14

1

17/08/2018 13:04:15

1

17/08/2018 13:04:16

1

17/08/2018 06:04:17

1

17/08/2018 06:04:18

1

 

 The second one is a Date Range, where there is date In, date end and Group

 

Date In   -    Date End    -  Group

01/08/2018 06:00:00

08/08/2018 05:59:00

Group 1

08/08/2018 06:00:00

15/08/2018 05:59:00

Group 2

15/08/2018 06:00:00

22/08/2018 05:59:00

Group 3

 

Basically I need to group/classify which transactions according to date range. Example:

 

01/08/2018 07:55:00

1


Is after 01/08/2018 06:00:00 and before 08/08/2018 05:59:00, then it will be Group 1. 

 

I looked over GROUP and BIN but in my mind the best solution will be create a IF AND statement. 

 

In my mind it sounds; 

 

New Column = IF(Transactions[DateTime] > 'Date Range'[Date In] && Transactions[DateTime] < 'Date Range'[Date End] ; 'Data Range'[Group]);BLANK()).

 

I'm kind of stuck creating this statement in unrelated tables, I will be very thankful receiving any help.

I'm open to any suggestions, any ideas, I'm familiar with DAX, Power Query and most of basic stuff in Power BI. 

 

Thanks in Advance

 

Ayrton S.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: if and statement on unrelated tables

@ayrtonsantos,

 

You may refer to the post below.

https://community.powerbi.com/t5/Desktop/conditional-join/m-p/340423#M152404

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.
2 REPLIES 2
Community Support Team
Community Support Team

Re: if and statement on unrelated tables

@ayrtonsantos,

 

You may refer to the post below.

https://community.powerbi.com/t5/Desktop/conditional-join/m-p/340423#M152404

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.
ayrtonsantos Frequent Visitor
Frequent Visitor

Re: if and statement on unrelated tables

It worked perfect and clear.

Thank you very much for showing a solution.

 

BI.PNG