Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |