Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
You may refer to the post below.
https://community.powerbi.com/t5/Desktop/conditional-join/m-p/340423#M152404
You may refer to the post below.
https://community.powerbi.com/t5/Desktop/conditional-join/m-p/340423#M152404
It worked perfect and clear.
Thank you very much for showing a solution.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |