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.
I am fairly new to PowerBI and really can seem to find a specific or even related solution online to my problem.
The issue:
Table1 is a list of Sales persons with Start date / End date and target values
Person | Start | End | Target |
SP1 | 10/01/2023 | 01/12/2023 | 10000 |
SP2 | 15/04/2023 | 19/05/2023 | 100 |
SP3 | 01/01/2023 | 31/08/2023 | 315 |
Table2 is a list of dates that starts from the List.Min(Start) to List.Max(End) of Table1.
I need to merge Table2 to Table1 but for every date (row level) in Table2 for each sales person within each start to end range.
the output would end up showing a row for every date in Table2 duplicated for each sales person between their respective Start and End date.
I have already played around with merge and creating additional join columns but I can't seem to get the correct output. I know this can also be done with Dax using crossjoin and filter but I have to build this into power query.
Thanks in advance
Solved! Go to Solution.
I need to merge Table2 to Table1
You don't need to do that. Merges are bad for your health.
Let the data model do the work for you. Worst case use GENERATESERIES and INTERSECT in DAX to solve this.
Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.
If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...
I need to merge Table2 to Table1
You don't need to do that. Merges are bad for your health.
Let the data model do the work for you. Worst case use GENERATESERIES and INTERSECT in DAX to solve this.
Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.
If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...
Thank you for the info - sadly I went the merge route.
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 |
---|---|
96 | |
95 | |
80 | |
74 | |
66 |
User | Count |
---|---|
130 | |
106 | |
105 | |
86 | |
72 |