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've been struggling with this one for a while. I know the solution is going to be fairly easy and I'll be kicking myself for having spent such a long time blanking on this. Here is what I'm trying to accomplish:
Calculate the total 'recon' (reconditioning) spend by vehicle. For each Stock #, I need the total RO $Amt from type 'Internal' only from FactServHist but only include RO's that fall between the Recon Start and Recon End dates found in FactInventory (inclusive of start/end dates).
I have 5 tables involved in this example:
Solved! Go to Solution.
Is FactInventory to DimInventory 1:1 or Many:1 ?
If 1:1, you can create a relationship between FactServHist and FactInventory, then your measure should be something like
TotalRecon = sumx( FILTER(FactServHistory, FactServHistory[Type]="INTERNAL" && FactServHistory[Date] >= RELATED(FactInventory[Recon Start]) && FactServHistory[Date] <= RELATED(FactInventory[Recon Finish])), FactServHistory[$AMT] )
If DimInventory : FactInventory is not 1:1, (and therefore FactServHistory : FactInventory = many : many) you'll probably need additional bridge tables to be able to find the right dates in the FactInventory table.
Hope this helps
David
Is FactInventory to DimInventory 1:1 or Many:1 ?
If 1:1, you can create a relationship between FactServHist and FactInventory, then your measure should be something like
TotalRecon = sumx( FILTER(FactServHistory, FactServHistory[Type]="INTERNAL" && FactServHistory[Date] >= RELATED(FactInventory[Recon Start]) && FactServHistory[Date] <= RELATED(FactInventory[Recon Finish])), FactServHistory[$AMT] )
If DimInventory : FactInventory is not 1:1, (and therefore FactServHistory : FactInventory = many : many) you'll probably need additional bridge tables to be able to find the right dates in the FactInventory table.
Hope this helps
David
Getting close! Looks like I need to clean up the source data a bit and tinker with the date ranges (it's not including RO's completed on the start/end dates but the ones in between are pulling in at least) but I'm getting some values at least. Thank you!
If the date on the FactServiceHistory table has a time component, but the dates on FactInventory does not, that may be where are seeing the discprepancies.
for example, 1/15/2017 14:35:00 > 1/15/2017 - even though both may look like just dates there could be hidden times.
Thanks for the additional suggestion. That was the first thing I checked. Great minds think alike 🙂
I ended up figuring out the issue - the source data coming from the system randomly was inserting a space in front of some of the RO #'s so once I went in and used the transform > trim feature everything is pulling over now. Thanks again for all your help!
I've been struggling with this one for a while. I know the solution is going to be fairly easy and I'll be kicking myself for having spent such a long time blanking on this. Here is what I'm trying to accomplish:
Calculate the total 'recon' (reconditioning) spend by vehicle. For each Stock #, I need the total RO $Amt from type 'Internal' only from FactServHist but only include RO's that fall between the Recon Start and Recon End dates found in FactInventory (inclusive of start/end dates).
I have 5 tables involved in this example:
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 |
---|---|
110 | |
97 | |
78 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |