Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Expand data to multiple rows or create a measure?


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.

fleet base tables.jpg

Here is the resulting table

fleet result.jpg

 

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

fleet usage example.jpg

 

I really appreciate your input.

2 REPLIES 2
AllisonKennedy
Super User
Super User

I believe you could solve this using a DimDate table. https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html

Put the DimDate[Date] on the axis and create your measure for usage in values.

Please @mention me in your reply if you want a response.

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

Anonymous
Not applicable

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"
)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.