The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I have a complex model that was brought in from Dynamics AX. In the data model, there is a 'Work Orders Table' that contains the field 'ACTUALEND' ( the end date for each work order). There is also an 'Objects' table containing the field 'Object Name'.
I'm pretty new to DAX and given the size of the workbook, I'd assume that creating a measure for these types of calculations would be preferreable to save space (this report also needs to be able to meet the size requirements for scheduled refresh using PowerBI online).
There is a 1 to Many relationship between Work Orders and Work Order Line using the Work Order RECID; and a Many to 1 relationship from Work Order Line to Object using the Object RECID.
Using this relationship, I'd like to add a measure to the values of this matrix showing the days between the most recent date for each row. In other words, the days between each work order for indivdual objects.
Ultimately, I'll use that measure to create another measure that computers the average days between work orders for all of the objects, using a visual that can be drilled down to show averages for individual objects.
I've tried getting this off the ground for a while and I'm pretty twisted around at this point. Any help in the right direction would be greatly apprciated.
Hi @d_brew,
You can try to use below measures if it suitable for your requirement:
Diff= var currDate=Max(WorkOrderLine[ActualEnd]) var currOrder=LASTNOBLANK(WorkOrderLine[WorkOrder],[WorkOrder]) var prevDate=MAXX(FILTER(ALL(WorkOrderLine),[WorkOrder]=currOrder&&[ActualEnd]<currDate),[ActualEnd]) return IF(prevDate=blank(),0,DateDiff(prevDate,currDate,Day)) AVG Per WorkOrder= var currOrder=LASTNOBLANK(WorkOrderLine[WorkOrder],[WorkOrder]) return AVERAGEX(FILTER(ALL(WorkOrderLine),[WorkOrder]=currOrder),[Diff]
If above is not help, can you provide some sample data to test?
Regards,
XIaoxin Sheng
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
94 | |
88 | |
71 | |
65 |
User | Count |
---|---|
241 | |
124 | |
120 | |
81 | |
79 |