problem with calculation-many to many

[MP_123]

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

[MP_123 · Accepted solution]

@v-jiascu-msft

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!

[MP_123]

please please help?

thank you

[v-jiascu-msft]

@MP_123

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]

@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

[MP_123]

how can I share an excel sample data?

[MP_123]

@v-jiascu-msft

hi,

tis calculated column is working and it's exactly what I need - 

Column = CALCULATE(sum(measure)),FILTER('cards Test','cards Test'[week]='Rolling-week'[week]))

the problem is that i want the column to change dynamically by other slicer than date.

**there is a solution to multiple each week 4 times, and add column of the Rolling28 date related, but i am trying to avoid this costing solution

[v-jiascu-msft]

@MP_123

Hi, 

It's my pleasure to help.

The column can't be changed dynamically by the slicer. The slicer works only in the report. Please post a sample here. You can upload the excel to online service like OneDriver or Dropbox, then share the link here. If these can't work, you can paste here directly with excel data.

Best Regards

Dale

[MP_123 · Accepted solution]

@v-jiascu-msft

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!

[v-jiascu-msft · Accepted solution]

@MP_123

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

[MP_123]

@v-jiascu-msft

thank you!

this is the solution i tried, but i was mistaken with the relationship

thanks!!!

[v-jiascu-msft]

@MP_123

It's my pleasure. I am so glad your problem has been solved.

Best Regards!

Dale