엑셀 Power Pivot에서 두 테이블을 연결하려고 할 때 관계를 만들 수 없거나, 피벗 테이블의 값이 반복되고 합계가 예상보다 크게 표시되는 경우가 있습니다.
한쪽 테이블의 키가 다른 쪽에서 여러 번 나타나는 일반적인 일대다 관계와 달리, 양쪽 키가 모두 반복되는 구조라면 다대다 관계일 가능성이 있습니다.
Excel 데이터 모델은 두 테이블 사이의 직접적인 다대다 관계를 지원하지 않습니다. 따라서 중복된 열끼리 바로 관계를 만들기보다, 어떤 값과 어떤 값이 연결되는지를 한 행씩 기록한 중간 테이블을 만들고 두 개의 일대다 관계로 나눠야 합니다.
다만 중간 테이블을 추가하는 것만으로 모든 필터와 합계가 자동으로 해결되는 것은 아닙니다. 중간 테이블의 연결 건수를 계산하는 경우와, 중간 테이블을 거쳐 별도의 매출 테이블을 계산하는 경우에는 필요한 DAX 측정값이 다릅니다.
이 글은 Microsoft의 Excel 데이터 모델 관계, Power Pivot 다이어그램 보기와 DAX 함수 공식 문서를 기준으로 작성했습니다. 예제의 테이블명과 열 이름은 실제 데이터 모델 구조에 맞게 변경해야 합니다.
 |
| Excel 데이터 모델은 두 테이블의 직접적인 다대다 관계를 지원하지 않습니다. 중간 테이블로 관계를 나눈 뒤 분석 대상에 따라 DAX 측정값을 구성해야 합니다. 위 이미지는 관계 구조를 설명하기 위한 대표 개념 이미지입니다. |
먼저 일대다 관계와 다대다 관계를 구분합니다
학생과 교육 과정의 연결을 예로 들면 한 학생은 여러 과정을 수강할 수 있고, 하나의 과정에도 여러 학생이 등록할 수 있습니다.
| 테이블 | 주요 열 | 값의 반복 여부 |
|---|---|---|
| Students | StudentID | 학생마다 한 번만 존재 |
| Courses | CourseID | 과정마다 한 번만 존재 |
| Enrollments | StudentID, CourseID | 학생과 과정의 연결에 따라 반복 |
Students와 Courses를 직접 연결할 공통 열은 없습니다. 학생 한 명이 여러 과정에 연결되고, 과정 하나도 여러 학생에 연결되므로 두 테이블의 관계는 다대다입니다.
Enrollments 테이블은 어떤 학생이 어떤 과정에 등록했는지를 한 행씩 기록합니다.
| StudentID | CourseID |
|---|---|
| S001 | C101 |
| S001 | C205 |
| S002 | C101 |
이 중간 테이블을 이용하면 직접적인 다대다 관계를 다음 두 관계로 나눌 수 있습니다.
Students[StudentID]
1 → 여러 개
Enrollments[StudentID]
Courses[CourseID]
1 → 여러 개
Enrollments[CourseID]
두 원본 테이블의 중복 열을 직접 연결하면 안 되는 이유
Excel 데이터 모델의 관계에서는 한쪽 열이 각 행을 고유하게 식별할 수 있어야 합니다. 조회 테이블 쪽 관계 열에 중복 값이 있으면 일대다 관계의 ‘1’ 쪽으로 사용할 수 없습니다.
예를 들어 다음 두 열을 직접 연결하려고 하면 양쪽 모두 같은 ID가 여러 번 나타날 수 있습니다.
- 학생별 수강 내역의 CourseID
- 과정별 학생 내역의 CourseID
어느 쪽도 CourseID가 고유하지 않다면 관계의 조회 쪽을 결정할 수 없습니다. 이 상태에서 중복 값을 임의로 삭제하면 실제 학생과 과정의 연결 정보가 사라질 수 있습니다.
Students 테이블에서는 StudentID가 한 번만 있어야 합니다.
Courses 테이블에서는 CourseID가 한 번만 있어야 합니다.
Enrollments 테이블에서는 StudentID와 CourseID가 각각 반복될 수 있습니다.
다만 같은 학생과 같은 과정의 조합이 중복된 행은 업무 의미를 확인한 뒤 정리해야 합니다.
Power Query로 중간 테이블 만들기
원본 수강 내역에 StudentID와 CourseID가 함께 있다면 Power Query에서 두 열만 남기고 빈 값과 중복된 연결 조합을 제거해 중간 테이블을 만들 수 있습니다.
다음 예제는 현재 통합 문서의 tblEnrollmentRaw 테이블을
원본으로 사용합니다.
let
Source =
Excel.CurrentWorkbook(){[Name="tblEnrollmentRaw"]}[Content],
SelectedColumns =
Table.SelectColumns(
Source,
{
"StudentID",
"CourseID"
}
),
ChangedType =
Table.TransformColumnTypes(
SelectedColumns,
{
{"StudentID", type text},
{"CourseID", type text}
}
),
TrimmedText =
Table.TransformColumns(
ChangedType,
{
{
"StudentID",
each if _ = null then null else Text.Trim(_),
type text
},
{
"CourseID",
each if _ = null then null else Text.Trim(_),
type text
}
}
),
RemovedBlankRows =
Table.SelectRows(
TrimmedText,
each
[StudentID] <> null and
[StudentID] <> "" and
[CourseID] <> null and
[CourseID] <> ""
),
BridgeTable =
Table.Distinct(
RemovedBlankRows,
{
"StudentID",
"CourseID"
}
)
in
BridgeTable이 쿼리는 다음 순서로 중간 테이블을 만듭니다.
- 학생 ID와 과정 ID 열만 선택합니다.
- 두 열의 데이터 형식을 텍스트로 통일합니다.
- 키 앞뒤의 공백을 제거합니다.
- 학생 ID나 과정 ID가 없는 행을 제외합니다.
- 같은 학생·과정 조합이 중복된 경우 한 행만 남깁니다.
동일한 학생이 같은 과정을 여러 번 수강한 기록을 별도 거래로 유지해야 한다면 마지막 중복 제거 단계는 사용하면 안 됩니다. 중복 행이 오류인지, 재수강이나 회차별 등록을 의미하는지 먼저 확인해야 합니다.
중간 테이블과 차원 테이블을 데이터 모델에 로드합니다
Students, Courses와 Enrollments 쿼리를 만든 뒤 각 쿼리를 데이터 모델에 로드합니다.
- Power Query 편집기에서 홈 → 닫기 및 다음으로 로드를 선택합니다.
- 연결만 만들기를 선택합니다.
- 이 데이터를 데이터 모델에 추가를 선택합니다.
- 세 테이블이 모두 데이터 모델에 포함됐는지 확인합니다.
Excel 버전과 창 크기에 따라 메뉴 문구나 위치가 일부 다르게 보일 수 있습니다.
Power Pivot 다이어그램 보기에서 두 관계를 만듭니다
Excel 리본 메뉴에서 Power Pivot → 관리를 연 다음 다이어그램 보기로 전환합니다.
첫 번째 관계는 학생 테이블과 중간 테이블 사이에 만듭니다.
Students[StudentID] → Enrollments[StudentID]
두 번째 관계는 과정 테이블과 중간 테이블 사이에 만듭니다.
Courses[CourseID] → Enrollments[CourseID]
Students[StudentID]와 Courses[CourseID]는 각각 중복 없는 고유 열이어야 합니다. Enrollments 쪽 ID는 여러 행에서 반복될 수 있습니다.
관계를 만들기 전에 키 열 네 가지를 확인합니다
1. 조회 테이블의 키가 고유한지 확인
Students 테이블에 같은 StudentID가 두 행 이상 있거나 Courses 테이블에 같은 CourseID가 반복되면 해당 열을 관계의 ‘1’ 쪽으로 사용할 수 없습니다.
이름이 같은 학생이나 과정이 있을 수 있으므로 이름 열보다 변경되지 않는 고유 ID를 관계 키로 사용하는 편이 적절합니다.
2. 두 관계 열의 데이터 형식을 맞춤
Students[StudentID]가 텍스트인데 Enrollments[StudentID]가 정수라면 같은 값처럼 보여도 관계를 만들 수 없거나 일치하지 않을 수 있습니다.
각 관계에 사용하는 두 열의 데이터 형식을 동일하게 맞춥니다.
3. 앞뒤 공백과 보이지 않는 문자 확인
S001과 S001 은 화면에서는 비슷해 보여도
서로 다른 텍스트 값입니다. Power Query에서 Text.Trim과
필요한 정리 단계를 적용합니다.
4. 조회 테이블에 없는 ID 확인
Enrollments에 S999가 있지만 Students에 S999가 없다면 해당 연결은 학생 테이블의 행과 일치하지 않습니다.
Excel은 관계 열의 데이터 형식이 맞는지는 확인하지만 실제 모든 값이 서로 대응하는지까지 보장하지 않습니다. 관계를 만든 뒤 두 테이블의 필드를 함께 사용한 피벗 테이블로 결과를 확인해야 합니다.
연결 건수를 계산할 때는 중간 테이블을 측정합니다
학생별 등록 과정 수나 과정별 등록 학생 수처럼 중간 테이블 자체의 연결을 계산하려면 Enrollments 테이블의 행 수를 측정값으로 사용할 수 있습니다.
등록 건수 :=
COUNTROWS(Enrollments)피벗 테이블 행에 Students의 학생 이름을 넣고 값에 등록 건수를 넣으면 학생별 수강 등록 행 수를 계산할 수 있습니다.
피벗 테이블 행에 Courses의 과정 이름을 넣으면 과정별 등록 건수를 계산할 수 있습니다.
학생·과정 조합이 중복 제거된 중간 테이블이라면 등록 건수는 고유한 학생·과정 연결 수를 의미합니다. 동일한 조합의 중복 행을 유지했다면 연결 수가 아니라 원본 등록 행 수를 계산합니다.
학생 수와 과정 수는 고유 개수로 계산합니다
현재 필터에서 서로 다른 학생 수를 계산하려면 다음 측정값을 사용합니다.
고유 학생 수 :=
DISTINCTCOUNT(Enrollments[StudentID])서로 다른 과정 수는 다음과 같이 계산합니다.
고유 과정 수 :=
DISTINCTCOUNT(Enrollments[CourseID])DISTINCTCOUNT는 빈 값도 하나의 고유 값으로 계산할 수 있으므로, 앞의 Power Query 단계에서 빈 ID를 제거한 뒤 사용하는 것이 결과를 해석하기 쉽습니다.
중간 테이블만 추가해도 별도 매출 테이블이 자동 필터링되는 것은 아닙니다
다음과 같이 제품과 태그가 다대다로 연결되고, 매출은 별도 Sales 테이블에 저장된 구조를 생각할 수 있습니다.
- Products: 제품별 한 행
- Tags: 태그별 한 행
- ProductTag: 제품과 태그 연결
- Sales: 제품별 매출 거래
관계는 다음과 같이 만들 수 있습니다.
Tags[TagID] → ProductTag[TagID]
Products[ProductID] → Sales[ProductID]
태그 필터는 ProductTag의 연결 행을 필터링하지만, ProductTag에서 다시 Products를 거쳐 Sales로 자동 전달된다고 가정하면 안 됩니다.
먼저 일반 매출 측정값을 만듭니다.
총매출 :=
SUM(Sales[SalesAmount])태그에 연결된 제품 목록을 찾아 Products 테이블에 필터로 적용하는 예제 측정값은 다음과 같습니다.
태그별 매출 :=
CALCULATE(
[총매출],
FILTER(
VALUES(Products[ProductID]),
CALCULATE(
COUNTROWS(ProductTag)
) > 0
)
)이 측정값은 다음 순서로 작동합니다.
- 현재 보고서 필터에서 제품 ID 목록을 가져옵니다.
- 각 제품이 현재 선택된 태그와 ProductTag에서 연결되는지 확인합니다.
- 연결 행이 있는 제품만 남깁니다.
- 선택된 제품 필터를 Sales 테이블의 총매출 계산에 적용합니다.
위 수식은 Products, ProductTag와 Sales라는 예제 구조에 맞춘 측정값입니다. 실제 모델의 테이블명, 관계 방향과 계산 목적이 다르면 그대로 복사하지 말고 구조에 맞춰 수정해야 합니다.
다대다 결과의 행 합계와 전체 합계가 다를 수 있습니다
제품 하나가 태그 A와 태그 B에 모두 연결돼 있다면 그 제품의 매출은 태그 A 행과 태그 B 행에 각각 포함될 수 있습니다.
예를 들어 한 제품의 매출이 100,000원이고 태그가 두 개라면 다음처럼 표시될 수 있습니다.
| 태그 | 매출 |
|---|---|
| 신상품 | 100,000원 |
| 추천상품 | 100,000원 |
두 행을 단순히 더하면 200,000원이지만 실제 제품 매출은 100,000원입니다. 여러 분류에 동시에 속할 수 있는 다대다 구조에서는 행별 값의 합이 전체 고유 합계와 일치하지 않을 수 있습니다.
이것을 무조건 계산 오류로 처리하면 안 됩니다. 보고서의 목적이 각 태그에 연결된 전체 매출을 보여주는 것인지, 매출을 태그 수로 나눠 배분해야 하는지 업무 규칙을 먼저 정해야 합니다.
제품 매출 전체를 각 태그에 반복 표시할지,
태그 수로 균등하게 나눌지,
중간 테이블의 배분율 열을 사용할지는 업무 기준에 따라 다릅니다.
Power Pivot이 적절한 배분 방식을 자동으로 선택해 주지는 않습니다.
중간 테이블에 배분율이 있다면 별도 계산이 필요합니다
한 거래 금액을 여러 담당자나 부서에 배분해야 한다면 중간 테이블에 배분율을 기록할 수 있습니다.
| ProductID | TagID | AllocationRate |
|---|---|---|
| P001 | T01 | 0.6 |
| P001 | T02 | 0.4 |
이 경우 단순한 태그별 매출 측정값이 아니라 제품 매출과 배분율을 함께 계산하는 별도 DAX가 필요합니다.
한 제품에 연결된 배분율 합계가 1인지, 기간별로 배분율이 바뀌는지, 거래별 배분율인지 제품별 배분율인지에 따라 모델 구조가 달라집니다. 업무 규칙이 정해지지 않은 상태에서 임의로 매출을 나누면 실제 보고 금액과 달라질 수 있습니다.
관계는 만들어졌지만 결과가 비어 있거나 반복될 때
조회 테이블에 없는 ID가 있는 경우
중간 테이블의 ID가 Students나 Courses에 없다면 해당 행은 조회 테이블의 이름과 연결되지 않습니다. 빈 항목으로 보이거나 예상한 분류에 포함되지 않을 수 있습니다.
키 열의 데이터 형식이 다른 경우
한쪽은 정수 101이고 다른 쪽은 텍스트 “101”이면 같은 값처럼 보여도 관계에서 일치하지 않을 수 있습니다. Power Query 또는 Power Pivot에서 데이터 형식을 맞춥니다.
중간 테이블에 연결 조합이 중복된 경우
같은 StudentID와 CourseID 조합이 두 번 있으면 COUNTROWS 측정값도 두 건으로 계산합니다. 실제로 두 번 등록된 것인지 중복 수집된 것인지 확인해야 합니다.
차원 테이블의 키가 중복된 경우
Students에 S001이 두 행 있으면 StudentID를 관계의 ‘1’ 쪽으로 사용할 수 없습니다. 이름, 이메일 등 다른 값이 달라도 학생 ID가 같은 행은 하나의 학생으로 합쳐야 하는지 ID 자체가 잘못된 것인지 확인합니다.
측정값 없이 숫자 열을 바로 합산한 경우
피벗 테이블에 중간 테이블과 별도 사실 테이블의 필드를 함께 넣었다고 필터가 모든 경로를 자동으로 통과하는 것은 아닙니다. 분석할 숫자는 관계 구조에 맞는 측정값으로 계산해야 합니다.
모델에 순환 경로를 추가하지 않습니다
다대다 문제를 해결하려고 기존 관계를 유지한 채 중간 테이블 관계까지 모두 추가하면 테이블 사이에 여러 필터 경로나 순환 구조가 생길 수 있습니다.
Excel 데이터 모델은 테이블 간 관계가 고리 형태가 되는 구조를 허용하지 않습니다. 같은 두 테이블 사이에 관계가 여러 개 있을 때도 한 관계만 활성 상태로 사용할 수 있습니다.
관계를 추가하기 전에 Power Pivot의 다이어그램 보기에서 기존 연결선을 확인하고, 같은 테이블로 가는 경로가 중복되지 않는지 살펴봅니다.
다대다 관계를 수정하는 순서
두 테이블의 관계 열에 모두 중복이 있다면 직접적인 일대다 관계를 만들 수 없습니다.
학생, 과정, 제품, 태그처럼 각 ID가 한 번만 존재하는 테이블을 만듭니다.
어떤 ID와 어떤 ID가 연결되는지를 한 행씩 기록합니다.
각 차원 테이블의 고유 키를 중간 테이블의 반복 키와 연결합니다.
COUNTROWS와 DISTINCTCOUNT를 이용해 등록 건수와 고유 ID 수를 계산합니다.
중간 테이블을 추가한 것만으로 필터가 매출 테이블까지 자동 전달된다고 가정하지 않습니다.
하나의 거래가 여러 분류에 포함되면 각 행의 합과 고유 전체 합계가 다를 수 있습니다.
내용 확인에 사용한 Microsoft 공식 자료
공식 문서 확인일: 2026년 7월 29일
댓글 쓰기