Forum Discussion
Post PatronHelp with relationships
Super UserHey,
I guess you are looking for a measure like this:
Measure =
CALCULATE(
SUM('Invoice'[[Invoice]] Amount])
, CROSSFILTER('Job List'[[Job]] Job No.] , 'Time'[[Job]] Job No.] , Both)
)
Using CROSSFILTER allows to avoid many-to-many relationships, but still provides the possibility to use the filter direction "Both".
This allows to create something like this:
But, I'm not sure how you want to treat the fact, that Brenton and Chris have worked on job number "1901-0001" and for this reason the measure shows the same value. But this is not an issue of the measure, but this is due to data distribution. Of course this can be used to calculate some kind of "share".
Hopefully this is what you are looking.
Regards,
Tom
- Pmorg73
Hi. Might be useful. However. This is my companies invoice against time. We have 50 staff, and typically 3 or 4 people will owkr on any one job. My director asked me to be able to pick out monthly totals for each staff member if I can. So I the duplication of the total will not quite get me what I need.
I will certainly study the measure suggested and may use it in some cases.
I have wtached videos where they talk about this totals not carrying across tables, but they fix it with relationships. I can never make it work :(
- TomMartens
Hey,
as there is no clue in the data how the amount from the invoice table is related to a staff member the "correct" number (from a technical point of view) is provided by the measure, this can not be solved by data modeling, just by additional information or by some kind of business rule like this
per staff member and job no calculateMeasure * DIVIDE(individual hours, sum of hours by all staff members)
Regards,
Tom
- Pmorg73
Food for thought. Many thanks
- Pmorg73
I am struggling to apply this to my model. I want the column "invoiced Amount total" from table "Invoiced Time Report" to propogate in the lower visual combined with the table "Workflow Time Report". Getting this same result everywhich way I try to do this. ?????
My cross filter measure is
Inv Time measure 2 = CALCULATE(SUM('Invoiced Time Report'[[Task]] Amount]) , CROSSFILTER('Job List'[Job No.] ,'Workflow Time Report'[[Job]] Job No.], Both) )
