Hello - I need some assistance with calculating a measure. Here is what I have: Table A: Table B: These two tables are joined through a bridge table by Account Number/Billing Telephone Number as both have repeating acocunt numbers and EOM Load Date values. There are other tables joined through the bridge table as well. What I want to do on Table A is: SUM(USOC_Charges) from Table B If A.EOM Load Date=B.EOM_Load_Date. Account Number would also be equal to Billing Telephone Number but I think that is acomplished through the bridge table. I have tried SUMX, Calculate(Sum(Filter(... a few other things but for the life of me cannot figure out how to get this to work. Any help is appreciated. Matt

Hi matthew_hampton Create relationships between Table A and Table B by Bridge Table Calculate sum of USOC Charges where AccountID = AccountID and EOM Load Date = EOM Load Date Measure = CALCULATE ( SUM ( Sheet2[ USOC_Charge ] ), FILTER ( ALL ( Sheet2 ), [AccountID] = SELECTEDVALUE ( Sheet2[AccountID] ) && [EOM Load Date] = SELECTEDVALUE ( Sheet2[EOM Load Date] ) ) ) How do you want to use Period (in Table A) in visualization? How does Period (in Table A) affect sum of USOC Charges? Could you make me clear about these? Best Regards Maggie

Measure Help Across Tables With Bridge | Microsoft Fabric Community

3 Replies

MFelix

Super User

8 years ago

Hi matthew_hampton,

Can you provide your data in a table format or an in Excel format as you can imagine to duplicate the information you have and give you some help is difficult to replicate the data you have by images only, also provide the bridge table and expected result.

Thank you.

Regards,

MFelix

matthew_hampton

Helper I

8 years ago

Hi MFelix

Attached are three Excel snapshots of the table structure.

What I am looking to do is create visualizations using Period (in Table A) where USOC Charges (In Table B) are summed where AccountID = AccountID and EOM Load Date = EOM Load Date. That will tie charges to an account number at a specified period.

Bridge Table:

AccountID

12345

56789

98765

Table A

AccountID	EOM Load Date	Period
12345	3/1/18 12:00 AM	-1
12345	4/1/18 12:00 AM	0
12345	5/1/18 12:00 AM	1
12345	6/1/18 12:00 AM	2
12345	7/1/18 12:00 AM	3
12345	8/1/18 12:00 AM	4
12345	9/1/18 12:00 AM	5
12345	10/1/18 12:00 AM	6
12345	11/1/18 12:00 AM	7
12345	12/1/18 12:00 AM	8
12345	1/1/19 12:00 AM	9
12345	2/1/19 12:00 AM	10
12345	3/1/19 12:00 AM	11
12345	4/1/19 12:00 AM	12
56789	3/1/18 12:00 AM	-1
56789	4/1/18 12:00 AM	0
56789	5/1/18 12:00 AM	1
56789	6/1/18 12:00 AM	2
56789	7/1/18 12:00 AM	3
56789	8/1/18 12:00 AM	4
56789	9/1/18 12:00 AM	5
56789	10/1/18 12:00 AM	6
56789	11/1/18 12:00 AM	7
56789	12/1/18 12:00 AM	8
56789	1/1/19 12:00 AM	9
56789	2/1/19 12:00 AM	10
56789	3/1/19 12:00 AM	11
56789	4/1/19 12:00 AM	12
98765	1/1/18 12:00 AM	-1
98765	2/1/18 12:00 AM	0
98765	3/1/18 12:00 AM	1
98765	4/1/18 12:00 AM	2
98765	5/1/18 12:00 AM	3
98765	6/1/18 12:00 AM	4
98765	7/1/18 12:00 AM	5
98765	8/1/18 12:00 AM	6
98765	9/1/18 12:00 AM	7
98765	10/1/18 12:00 AM	8
98765	11/1/18 12:00 AM	9
98765	12/1/18 12:00 AM	10
98765	1/1/19 12:00 AM	11
98765	2/1/19 12:00 AM	12

Table B

AccountID	EOM Load Date	USOC_Charge
12345	3/1/18 12:00 AM	$ 77.55
12345	4/1/18 12:00 AM	$ 22.62
12345	5/1/18 12:00 AM	$ 69.33
12345	6/1/18 12:00 AM	$ 78.06
12345	7/1/18 12:00 AM	$ 0.70
12345	8/1/18 12:00 AM	$ 118.66
12345	9/1/18 12:00 AM	$ 94.46
12345	10/1/18 12:00 AM	$ 92.28
12345	11/1/18 12:00 AM	$ 76.57
12345	12/1/18 12:00 AM	$ 60.30
12345	1/1/19 12:00 AM	$ 64.74
12345	2/1/19 12:00 AM	$ 29.28
12345	3/1/19 12:00 AM	$ 33.94
12345	4/1/19 12:00 AM	$ 117.38
56789	3/1/18 12:00 AM	$ 55.13
56789	4/1/18 12:00 AM	$ 26.41
56789	5/1/18 12:00 AM	$ 109.21
56789	6/1/18 12:00 AM	$ 9.35
56789	7/1/18 12:00 AM	$ 27.80
56789	8/1/18 12:00 AM	$ 106.71
56789	9/1/18 12:00 AM	$ 32.18
56789	10/1/18 12:00 AM	$ 72.95
56789	11/1/18 12:00 AM	$ 100.94
56789	12/1/18 12:00 AM	$ 35.34
56789	1/1/19 12:00 AM	$ 86.40
56789	2/1/19 12:00 AM	$ 95.15
56789	3/1/19 12:00 AM	$ 57.72
56789	4/1/19 12:00 AM	$ 19.50
98765	1/1/18 12:00 AM	$ 56.95
98765	2/1/18 12:00 AM	$ 1.41
98765	3/1/18 12:00 AM	$ 104.44
98765	4/1/18 12:00 AM	$ 92.68
98765	5/1/18 12:00 AM	$ 44.42
98765	6/1/18 12:00 AM	$ 113.94
98765	7/1/18 12:00 AM	$ 24.90
98765	8/1/18 12:00 AM	$ 55.38
98765	9/1/18 12:00 AM	$ 76.33
98765	10/1/18 12:00 AM	$ 47.40
98765	11/1/18 12:00 AM	$ 3.56
98765	12/1/18 12:00 AM	$ 68.52
98765	1/1/19 12:00 AM	$ 71.79
98765	2/1/19 12:00 AM	$ 77.53
12345	3/1/18 12:00 AM	$ 60.96
12345	4/1/18 12:00 AM	$ 103.70
12345	5/1/18 12:00 AM	$ 76.17
12345	6/1/18 12:00 AM	$ 60.20
12345	7/1/18 12:00 AM	$ 50.82
12345	8/1/18 12:00 AM	$ 99.11
12345	9/1/18 12:00 AM	$ 94.23
12345	10/1/18 12:00 AM	$ 23.97
12345	11/1/18 12:00 AM	$ 60.65
12345	12/1/18 12:00 AM	$ 66.46
12345	1/1/19 12:00 AM	$ 113.50
12345	2/1/19 12:00 AM	$ 117.31
12345	3/1/19 12:00 AM	$ 70.51
12345	4/1/19 12:00 AM	$ 69.50
56789	3/1/18 12:00 AM	$ 101.76
56789	4/1/18 12:00 AM	$ 47.67
56789	5/1/18 12:00 AM	$ 21.09
56789	6/1/18 12:00 AM	$ 43.91
56789	7/1/18 12:00 AM	$ 34.22
56789	8/1/18 12:00 AM	$ 114.06
56789	9/1/18 12:00 AM	$ 91.24
56789	10/1/18 12:00 AM	$ 70.55
56789	11/1/18 12:00 AM	$ 37.39
56789	12/1/18 12:00 AM	$ 45.25
56789	1/1/19 12:00 AM	$ 97.57
56789	2/1/19 12:00 AM	$ 19.61
56789	3/1/19 12:00 AM	$ 58.42
56789	4/1/19 12:00 AM	$ 106.59
98765	1/1/18 12:00 AM	$ 97.24
98765	2/1/18 12:00 AM	$ 25.53
98765	3/1/18 12:00 AM	$ 65.36
98765	4/1/18 12:00 AM	$ 112.28
98765	5/1/18 12:00 AM	$ 7.49
98765	6/1/18 12:00 AM	$ 55.21
98765	7/1/18 12:00 AM	$ 96.36
98765	8/1/18 12:00 AM	$ 20.20
98765	9/1/18 12:00 AM	$ 11.71
98765	10/1/18 12:00 AM	$ 54.52
98765	11/1/18 12:00 AM	$ 55.53
98765	12/1/18 12:00 AM	$ 14.94
98765	1/1/19 12:00 AM	$ 6.33
98765	2/1/19 12:00 AM	$ 37.64
12345	3/1/18 12:00 AM	$ 89.76
12345	4/1/18 12:00 AM	$ 69.25
12345	5/1/18 12:00 AM	$ 63.32
12345	6/1/18 12:00 AM	$ 65.18
12345	7/1/18 12:00 AM	$ 65.86
12345	8/1/18 12:00 AM	$ 106.23
12345	9/1/18 12:00 AM	$ 51.29
12345	10/1/18 12:00 AM	$ 65.64
12345	11/1/18 12:00 AM	$ 102.04
12345	12/1/18 12:00 AM	$ 68.47
12345	1/1/19 12:00 AM	$ 47.55
12345	2/1/19 12:00 AM	$ 99.94
12345	3/1/19 12:00 AM	$ 75.78
12345	4/1/19 12:00 AM	$ 41.39
56789	3/1/18 12:00 AM	$ 5.50
56789	4/1/18 12:00 AM	$ 56.80
56789	5/1/18 12:00 AM	$ 112.26
56789	6/1/18 12:00 AM	$ 32.94
56789	7/1/18 12:00 AM	$ 22.11
56789	8/1/18 12:00 AM	$ 34.50
56789	9/1/18 12:00 AM	$ 60.07
56789	10/1/18 12:00 AM	$ 48.60
56789	11/1/18 12:00 AM	$ 117.86
56789	12/1/18 12:00 AM	$ 1.63
56789	1/1/19 12:00 AM	$ 7.50
56789	2/1/19 12:00 AM	$ 24.54
56789	3/1/19 12:00 AM	$ 48.61
56789	4/1/19 12:00 AM	$ 119.79
98765	1/1/18 12:00 AM	$ 81.06
98765	2/1/18 12:00 AM	$ 21.01
98765	3/1/18 12:00 AM	$ 4.22
98765	4/1/18 12:00 AM	$ 18.24
98765	5/1/18 12:00 AM	$ 97.74
98765	6/1/18 12:00 AM	$ 35.11
98765	7/1/18 12:00 AM	$ 65.60
98765	8/1/18 12:00 AM	$ 83.68
98765	9/1/18 12:00 AM	$ 83.75
98765	10/1/18 12:00 AM	$ 104.29
98765	11/1/18 12:00 AM	$ 62.05
98765	12/1/18 12:00 AM	$ 67.64
98765	1/1/19 12:00 AM	$ 87.09
98765	2/1/19 12:00 AM	$ 59.23
12345	3/1/18 12:00 AM	$ 32.63
12345	4/1/18 12:00 AM	$ 3.61
12345	5/1/18 12:00 AM	$ 16.01
12345	6/1/18 12:00 AM	$ 38.17
12345	7/1/18 12:00 AM	$ 16.29
12345	8/1/18 12:00 AM	$ 76.40
12345	9/1/18 12:00 AM	$ 77.10
12345	10/1/18 12:00 AM	$ 86.67
12345	11/1/18 12:00 AM	$ 78.53
12345	12/1/18 12:00 AM	$ 29.82
12345	1/1/19 12:00 AM	$ 64.31
12345	2/1/19 12:00 AM	$ 108.67
12345	3/1/19 12:00 AM	$ 104.45
12345	4/1/19 12:00 AM	$ 64.22
56789	3/1/18 12:00 AM	$ 30.77
56789	4/1/18 12:00 AM	$ 105.79
56789	5/1/18 12:00 AM	$ 110.69
56789	6/1/18 12:00 AM	$ 29.00
56789	7/1/18 12:00 AM	$ 9.79
56789	8/1/18 12:00 AM	$ 28.39
56789	9/1/18 12:00 AM	$ 59.57
56789	10/1/18 12:00 AM	$ 48.82
56789	11/1/18 12:00 AM	$ 9.41
56789	12/1/18 12:00 AM	$ 70.07
56789	1/1/19 12:00 AM	$ 20.06
56789	2/1/19 12:00 AM	$ 99.93
56789	3/1/19 12:00 AM	$ 57.90
56789	4/1/19 12:00 AM	$ 64.26
98765	1/1/18 12:00 AM	$ 67.86
98765	2/1/18 12:00 AM	$ 53.07
98765	3/1/18 12:00 AM	$ 39.21
98765	4/1/18 12:00 AM	$ 84.36
98765	5/1/18 12:00 AM	$ 6.87
98765	6/1/18 12:00 AM	$ 44.89
98765	7/1/18 12:00 AM	$ 101.80
98765	8/1/18 12:00 AM	$ 41.34
98765	9/1/18 12:00 AM	$ 31.54
98765	10/1/18 12:00 AM	$ 22.20
98765	11/1/18 12:00 AM	$ 19.28
98765	12/1/18 12:00 AM	$ 48.96
98765	1/1/19 12:00 AM	$ 110.47
98765	2/1/19 12:00 AM	$ 20.66

I could not figure out how to attach an Excel file so if this does not help, please let me know and I will provide sample data another way.

Matt

v-juanli-msft
Community Support
8 years ago
Hi matthew_hampton

Create relationships between Table A and Table B by Bridge Table

Calculate sum of USOC Charges where AccountID = AccountID and EOM Load Date = EOM Load Date

Measure = CALCULATE ( SUM ( Sheet2[ USOC_Charge ] ), FILTER ( ALL ( Sheet2 ), [AccountID] = SELECTEDVALUE ( Sheet2[AccountID] ) && [EOM Load Date] = SELECTEDVALUE ( Sheet2[EOM Load Date] ) ) )

How do you want to use Period (in Table A) in visualization?

How does Period (in Table A) affect sum of USOC Charges? Could you make me clear about these?

Best Regards

Maggie

Forum Discussion

Measure Help Across Tables With Bridge

3 Replies

Related content

Combining three CONCATENATEX Measures with results from two Tables

Error: The model doesn't have a measure named Measure on table

Measure Issue in visual table

Measure

New Measure Thrashes Table but Measure Proven to Work