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.
Hello there
New to PowerBI and trying to pick it up as a go along building a dashboard that will save me time every day.
I am trying to create a new column in my main data table that is based on a date and matching it to values in a different table.
For example:
Machine usage
Users PIN | Date of use |
2468 | 02/10/2017 |
2468 | 20/01/2018 |
3098 | 05/02/2018 |
2568 | 15/01/2018 |
2568 | 20/01/2018 |
Subscription history
Users PIN | Subscription | Subscription Start date | Subscription End Date |
2468 | Bronze | 01/10/2017 | 25/11/2017 |
2468 | Silver | 25/11/2017 | 15/01/2018 |
2468 | Gold | 15/01/2018 | 15/01/2019 |
3098 | Gold | 20/10/2017 | 02/02/2018 |
3098 | Bronze | 02/02/2018 | 02/02/2019 |
2568 | Silver | 01/01/2018 | 10/01/2018 |
2568 | Bronze | 10/01/2018 | 10/01/2019 |
What I am looking to do is to add to the machine usage table the details of the subscription that was active at the time of use, eg:
Users PIN | Date of use | Subscription | Subscription Start date | Subscription End Date |
2468 | 02/10/2017 | Bronze | 01/10/2017 | 25/11/2017 |
2468 | 20/01/2018 | Gold | 15/01/2018 | 15/01/2019 |
3098 | 05/02/2018 | Bronze | 02/02/2018 | 02/02/2019 |
2568 | 15/01/2018 | Bronze | 10/01/2018 | 10/01/2019 |
2568 | 20/01/2018 | Bronze | 10/01/2018 | 10/01/2019 |
I am going to carry on reading and watching training videos - but right now I am not sure where and how best to achieve this.
Many thanks in advance.
Andy
Solved! Go to Solution.
You can use the same pattern as above to get the other columns you want for example
Subscription Start Date = CALCULATE ( FIRSTNONBLANK ( SubscriptionHistory[Subscription Start date], 1 ), FILTER ( SubscriptionHistory, MachineUsage[Users PIN] = SubscriptionHistory[Users PIN] && MachineUsage[Date of use] >= SubscriptionHistory[Subscription Start date] && MachineUsage[Date of use] <= SubscriptionHistory[Subscription End Date] ) )
That looks to do exactly what I wanted, thank you for your help. Now I am going to spend an hour making sure I understand how this works 🙂
Thanks
Andy
Hi @AndyT9
Try this Calculated Column....
Subscription = CALCULATE ( FIRSTNONBLANK ( SubscriptionHistory[Subscription], 1 ), FILTER ( SubscriptionHistory, MachineUsage[Users PIN] = SubscriptionHistory[Users PIN] && MachineUsage[Date of use] >= SubscriptionHistory[Subscription Start date] && MachineUsage[Date of use] <= SubscriptionHistory[Subscription End Date] ) )
You can use the same pattern as above to get the other columns you want for example
Subscription Start Date = CALCULATE ( FIRSTNONBLANK ( SubscriptionHistory[Subscription Start date], 1 ), FILTER ( SubscriptionHistory, MachineUsage[Users PIN] = SubscriptionHistory[Users PIN] && MachineUsage[Date of use] >= SubscriptionHistory[Subscription Start date] && MachineUsage[Date of use] <= SubscriptionHistory[Subscription End Date] ) )
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |