엑셀 DAX 최적화 – 성능 향상을 위한 모델링 & 함수 활용법
엑셀 파워피벗(Power Pivot)과 파워BI(Power BI)에서 DAX(Data Analysis Expressions)를 사용하다 보면, 데이터 규모가 커질수록 성능 저하가 발생할 수 있습니다. 수십만 건 이상의 데이터가 포함된 모델에서 복잡한 DAX 함수를 사용하면 보고서가 느려지고, 새로고침 시간이 길어지며, 심지어 오류가 발생할 수도 있습니다. 이번 글에서는 제가 실제 프로젝트에서 경험한 사례를 바탕으로, DAX 최적화 방법과 모델링 전략, 효율적인 함수 활용법을 상세히 정리합니다.
1) 왜 DAX 최적화가 필요한가?
엑셀과 파워BI는 기본적으로 인메모리 엔진을 사용합니다. 즉, 데이터가 메모리에 로드된 상태에서 DAX 계산이 이루어지기 때문에, 모델링과 수식이 비효율적이면 성능 저하가 발생합니다.
주요 성능 저하 원인:
- 불필요하게 많은 계산 열(Calculated Column)
- 중복 데이터와 비정규화된 테이블
- 복잡한 FILTER, CALCULATE 중첩
- ALL, VALUES의 과도한 사용
2) 성능을 좌우하는 데이터 모델링
성능 최적화의 출발점은 모델링입니다. 좋은 모델링이란, 데이터를 불필요하게 중복하지 않고, 정규화된 차원(Dimension) 테이블과 사실(Fact) 테이블 구조를 유지하는 것입니다.
- 차원 테이블: 고객, 제품, 날짜, 지역
- 사실 테이블: 매출, 주문, 재고
- 관계: 고유 키(예: 고객ID, 제품코드)를 통해 연결
실무에서는 “하나의 큰 데이터 테이블”을 만드는 경우가 많지만, 이는 성능 저하의 원인입니다. 차원-사실 테이블 구조(스타 스키마)를 적용하면 DAX 계산이 단순해지고 성능이 개선됩니다.
3) 측정값(Measure) vs 계산 열(Calculated Column)
많은 사용자들이 계산 열을 남용하는데, 이는 성능 저하의 주요 원인입니다. 계산 열은 데이터가 로드될 때마다 모든 행을 계산하므로 모델이 무거워집니다. 반면 측정값(Measure)은 필요할 때만 계산되므로 훨씬 효율적입니다.
비효율적 예시:
이익 = 매출[금액] - 매출[원가] (계산 열)효율적 대안:
이익 = SUM(매출[금액]) - SUM(매출[원가]) (측정값)4) 효율적인 함수 활용법
모든 DAX 함수가 동일한 성능을 제공하지는 않습니다. 특히, 복잡한 조건 계산에서는 어떤 함수를 쓰느냐에 따라 성능 차이가 큽니다.
- CALCULATE: 컨텍스트 전환의 핵심, 하지만 FILTER 중첩 시 성능 저하
- SUMX, AVERAGEX: 행 단위 반복(iteration)이므로 대용량 데이터에서 주의 필요
- RELATED: 차원-사실 테이블 관계에서 효율적, 하지만 중복 사용은 위험
- LOOKUPVALUE: 단순 매핑에는 효율적이지만, 대규모 테이블에서는 성능 저하
5) FILTER/ALL/VALUES 최적화 전략
FILTER, ALL, VALUES는 강력하지만 남용하면 성능이 급격히 떨어집니다.
- FILTER: 복잡한 논리를 단일 수식에 몰아넣기보다, 중간 측정값으로 나누어 사용
- ALL: 전체 테이블이 아닌 특정 컬럼 수준에서만 사용 → 불필요한 데이터 스캔 방지
- VALUES: DISTINCTCOUNT와 함께 효율적으로 사용
예시:
=DIVIDE(SUM(매출[금액]), CALCULATE(SUM(매출[금액]), ALL(매출[지역])))위 공식은 지역별 매출 비중을 계산하지만, ALL(매출) 대신 ALL(매출[지역])만 사용하면 성능이 크게 향상됩니다.
6) 실무 최적화 베스트 프랙티스
- 계산 열 최소화: 가능하면 측정값으로 대체
- 필터 단순화: 복잡한 조건은 미리 파워쿼리에서 전처리
- 중복 데이터 제거: 날짜, 고객, 제품 테이블은 고유값만 유지
- 관계 최적화: 1:N 관계 유지, 다대다 관계는 피하기
- 인덱스 열 추가: 파워쿼리 단계에서 기본 키 생성
7) 프로젝트 사례: 매출·재무·인사 대시보드 최적화
제가 컨설팅했던 한 유통 기업의 사례를 공유합니다. 초기에는 50만 건 매출 데이터를 단일 테이블로 운영하며, 복잡한 FILTER와 CALCULATE가 중첩된 DAX를 사용했습니다. 그 결과 보고서 새로고침 시간이 5분 이상 걸렸습니다.
최적화 전략을 적용했습니다:
- 매출 데이터를 사실 테이블, 제품/고객/날짜를 차원 테이블로 분리
- 계산 열을 모두 제거하고, 측정값 기반 KPI로 변환
- ALL, FILTER 조건을 단순화하고, 일부 전처리는 파워쿼리에서 처리
결과적으로 보고서 로딩 시간이 20초 이내로 단축되었고, 경영진은 매일 아침 신속하게 KPI 대시보드를 확인할 수 있게 되었습니다.
8) 자주 묻는 질문(FAQ)
Q1. DAX 성능 최적화는 엑셀에서도 필요한가요, 파워BI에서만 중요한가요?
두 환경 모두 중요합니다. 파워BI는 대규모 데이터를 다루기 때문에 더욱 필수적입니다.
Q2. 계산 열을 전혀 쓰면 안 되나요?
아닙니다. 필요한 경우(고정 속성, 관계용 키 컬럼 등)는 계산 열이 유용합니다. 다만 과도하게 쓰지 않는 것이 중요합니다.
Q3. 파워쿼리 전처리와 DAX 계산은 어떻게 구분해야 하나요?
데이터 정제·변환은 파워쿼리에서, KPI 계산·지표 생성은 DAX에서 처리하는 것이 원칙입니다.