Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have achieved my desired result, I just want to know if there is a better way, because the way I have done it, results in a very large data set.
I want to see if this can be achieved in another way?
I have 3 source tables,
1) A Vehicle table which shows each vehicle and a valid from and to date
2) A Maintenance table which shows each date that the vehicle is being maintained
3) A Trip table which shows each date the vehicle did a trip and how far it travelled.
My goal is to get a chart or table of data that shows, for every single date between the vehicles valid from and to date,
is it available to use? (if there is a maintenance date, then it is not available) and how far it travelled on each date.
I have achieved this by expanding my vehicle table in Power Query (in M), so there is one row for every date between its validity dates.
Then for each row, I determine if it is available by doing a calculate with a filter to look up if there is maintenance on that date or if a trip was done on that date.
This creates a VERY LARGE dataset as I have thousands of records being expanded thousands of times.
Here is a basic example.
Here are my base tables.
Here is the resulting table
Here is an example of a chart I want to achieve (i have a measure that counts the rows, that shows the count per "usage" type
I really appreciate your input.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Thank you.
I am having trouble creating the measure.
This is what I have, but I am getting out of memory issues.
Use Measure =
Var var_maintenance = CALCULATE(
FIRSTNONBLANK('Maintenance Table'[Car],1),
FILTER('Maintenance Table',
'Maintenance Table'[Car] = RELATED(Vehicle Table[Car]) &&
'Maintenance Table'[Maintenance Date] = RELATED('Date'[Date])
)
)
return
SWITCH(TRUE(),
var_maintenance <> BLANK(), "Maintenance",
sum(Trips Table[Distance Travelled]) = BLANK(), "Not Used",
"Used"
)
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |