Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
I've got a document with 4 facts table. There are dependent from each others.
Table 1 = Requests.
Each request can generate one or more Trip (Table 2).
A trip can be comosed of one or more services (Table 3) (but services can also exist without being linked to a trip neither to a request).
A Service can have one or more assignment.
I've already managed to make a single fact table but I don't know how to manage each mesures.
My Full fact table seems like
Request ID Request Date Trip ID Trip Date Service ID Service Date Assign.ID Assign Date Measure Service
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 01/01/2012 1 02/01/2012 1 02/01/2012 1 02/01/2012 10
1 01/01/2012 1 02/01/2012 2 02/01/2012 2 02/01/2012 20
1 01/01/2012 1 02/01/2012 2 02/01/2012 3 02/01/2012 20
- - - - 3 02/01/2012 4 02/01/2012 30
2 01/01/2012 2 02/01/2012 4 02/01/2012 5 02/01/2012 40
My question is for the request 1 - service 2.
If i make sum on the service measure I wil have 50. What I want is 30 because 2 is the same service and it is duplicate because it is composed of 2 assignements but it should be counted only once.
How do you manage that?
I think using Aggr but I don't really understand how it works and I could use some help.
Thanks
caro.
Try:
Aggr(Sum([Measure Service]),[Request ID]) or you may need the DISTINCT qualifier in there:
Aggr(DISTINCT Sum([Measure Service]),[Request ID])
Hope this helps,
Jason
Hi Jason,
I don't really understand how the aggr works.
Could you explain it to me?
I know the distinct. Without the aggr(), I could use it for [Request ID] 1 and it would work but then if I don't select the request ID and I have 2 time the same measures for 2 different request ID, then I don't want the distinct on it.
Can Aggr solve this?
Thanks for your help
Caro.
I will explain it in my cowboy way
Aggr ignores dimensions. So if you want an expression to calulate using dimensions that aren't present in your table or you want to calculate something using only the first 2 dimensions in your report and not all 3, you would use aggr. Essentially you can build an entire straight table with no dimensions using aggr and still get the values you want. It is a resource intensive function, so use with caution on 'big' data sets
To answer your second question and using the above explanation
Aggr(DISTINCT Sum([Measure Service]),[Request ID],[Serivce ID]). Literally just add the dimension that has the logic you want to apply when summing the values you deem distinct
Cheers,
Byron