Forum Discussion
problem with calculation-many to many
hi
i have a problem with calculation of a measure
my final table looks like
Orig Date measureX
1 02/25/2017 2
1 03/04/2017 24
1 03/11/2017 223
1 03/18/2017 12
2 02/25/2017 250
orig=1 means weekly data, orig=2 means Rolling28 data
i want to have a slicer of date, and want to show the 4 related weeks. i.e if i select 2/25 on the slicer, the chart will show the 4 weeks on x axis and y will be the measure for each week.
the problem that i can't manipulate the data since the slicer sliced my table with 2/25 only (orig 1 and 2)
the solution here is many to many relationship with this table Rolling-Week:
Rolling Week
2/25/2017 2/25/2017
2/25/2017 3/4/2017
2/25/2017 3/11/2017
2/25/2017 3/18/2017
3/4/2017 3/4/2017
3/4/2017 3/11/2017
3/4/2017 3/18/2017
3/4/2017 3/25 /2017 . . .. etc
but the problem is that there is no way to manage many to many relationship in power bi
so: i created the dates table:
Date
2/25/2017
3/4/2017
. . .
and connected it one to many with the rolling-week table from above.
now, if I slice on 2/25/2017, I know what are the 4 weeks related.
the problem is - calculated the measure for each week: MEASURE =
CALCULATE (
sum'Final Table'[Measure X]),
(filter(ALL('Final Table'[week]),'FinalTable'[week]=max('Rolling-week'[Week]))
))
the problem is that i have to add an aggregation to the 'Rolling-week'[Week])
how can i calculaure the measure for EACH week?
thanks a lot
hi Dale,
thank you very much!
this is the sample
reminder: orig=1 means weekly data: 04/01 is 04/01 to 04/07
orig=2 means 28 days - 04/01 is 04/01-04/28
thanks!
Hi,
I am so sorry I still can’t make out the whole thing. But we can solve it step by step. I made a little mode with your sample.
1. Is this similar with yours?
2. Which columns in your sample are to be used?
3. What does the "measure X" mean? Can you post the formula if it has one.
4. Please have a look at the image in the bottom. Is this what you want? Or you can privide a sample visual.
Best Regards!
Dale
10 Replies
- MP_123
Microsoft Employee
please please help?
thank you
- v-jiascu-msft
Microsoft Employee
Hi,
Could you post a little sample of your source data please. I have some questions about this:
1. Is the “Final Table” your source data or the result you want.
2. What is sum'Final Table'[Measure X]) for.
3. Is MeasureX a measure or a calculated column.
Best Regards.
Dale
- MP_123
Microsoft Employee
v-jiascu-msftthsank you!
above is a little sample, if it's not enought i will share the whole table.
the final table is my source table, the measures i want to some is there.
measure X is a calculated measure
2. i want to have bar chart where X axis is the 4 weeks related, and Y axis is the measure
thanks again