Forum Replies Created
November 13, 2018 at 12:30 am in reply to: How to use a measure in another table with many to many relationship #11214
Apologies – I misunderstood what was happening!
After further investigation I can see that the tblResource is being filtered by the date. It is not being filtered by [Booking Group]. Ive now tried using the following:
=sum(tblTasksSimple[Effort])*calculate([ActualAvailableResource],tblResource[Booking Group]) without success.
and [ActualAvailableResource]=sumx(tblResource,tblResource[Available Hrs]*RELATED(tblCalendar[Daily % of Annual]))
Hope this helps.
I’ve now had a chance to implement your suggestions on my live data – and I’m getting there. However, (theres always a however) I don’t seem to be able to make my cumulative measure work. The measure for an individual months ‘Rectified’ data is:
[Calc Rectified]:=COUNTROWS( FILTER( CALCULATETABLE(‘OPDEF Signals Data’, USERELATIONSHIP(Calendar[Date],’OPDEF Signals Data'[Rectified])),’OPDEF Signals Data'[Raised]>=[StartDate] && ‘OPDEF Signals Data'[Raised]<=[EndDate]))
which is based on your models, but no matter what I do I don’t seem to be able to remove the column filter for start date in order to get a cumulative total.
Grateful if you could provide more advice!
2013 at work (where I need the answer) and 2016 at home (from which I produced the example data set attached above).
Many thanks for the reply, and the use of Filter obviously worked. However, I now find that the [Rectified] measure returns nothing. So I wonder whether the structure of the [Start Date] measure (definition below) is in conflict with the Filter statement? I have a Timeline that sets the period over which I want to view the data and [Start Date] needs to return the first date selected. I added the:
to remove the filtering caused by having those two columns on the Pivot otherwise the [Start Date] merely returns the start of each column. I wonder whether there is some conflict because of the USERELATIONSHIP. The output I need for [Rectified] should be a count of those OPDEFs raised since the start of the Timeline that were Rectified in the current month. For info the definition of [Start Date] is:
[Start Date]:=CALCULATE(MIN(‘Calendar'[Dates]), ALL(‘Calendar'[Year],’Calendar'[Short Month]),
I’d be really grateful for any further help or advice.
PS. This was sent from my work forum membership and the original post from the one I setup from home.
Firstly, your assumptions are all spot-on and apologies for the error in SumofEffort.
Secondly, your answer works perfectly for which I’m really grateful – I have been struggling with this for weeks and have been learning Powerpivot at the same time! Its a really useful tool, but quite a different way of thinking from my previous approach without the ‘Power’ tools.
Again, many thanks – the help is very much appreciated.