Power Pivot의 DAX(데이터 분석 식)

DAX(데이터 분석 식)는 처음에는 약간 위협적으로 들리겠지만 이름에 속지 마세요. DAX 기본은 매우 쉽게 이해할 수 있습니다. 가장 먼저 해야 할 일은 DAX가 프로그래밍 언어가 아니라는 것입니다. DAX는 수식 언어입니다. DAX를 사용하여 계산된 열 및 측정값 (계산된 필드라고도 함)에 대한 사용자 지정 계산을 정의할 수 있습니다. DAX에는 Excel 수식에 사용되는 일부 함수와 관계형 데이터로 작업하고 동적 집계를 수행하도록 설계된 추가 함수가 포함되어 있습니다.

DAX 수식 이해

DAX 수식은 Excel 수식과 매우 유사합니다. 만들려면 등호 뒤에 함수 이름 또는 식, 필요한 값 또는 인수를 입력해야 합니다. Excel과 마찬가지로 DAX는 문자열 작업, 날짜 및 시간을 사용하여 계산 수행 또는 조건부 값을 만드는 데 사용할 수 있는 다양한 함수를 제공합니다.

그러나 DAX 수식은 다음과 같은 중요한 면에서 다릅니다.

• 행 단위로 계산을 사용자 지정하려는 경우 DAX에는 현재 행 값 또는 관련 값을 사용하여 컨텍스트에 따라 달라지는 계산을 수행할 수 있는 함수가 포함되어 있습니다.
• DAX에는 단일 값이 아닌 테이블을 결과로 반환하는 함수 유형이 포함되어 있습니다. 이러한 함수를 사용하여 다른 함수에 입력을 제공할 수 있습니다.
• DAX의 시간 인텔리전스 함수를 사용하면 날짜 범위를 사용하여 계산하고 병렬 기간의 결과를 비교할 수 있습니다.

DAX 수식을 사용하는 위치

Power Pivot의 계산 열 또는 계산 필드에서 수식을 만들 수 있습니다.

계산 열

계산 열은 기존 파워 피벗 테이블에 추가하는 열입니다. 열에 값을 붙여넣거나 가져오는 대신 열 값을 정의하는 DAX 수식을 만듭니다. 피벗 테이블(또는 피벗 차트)에 파워 피벗 테이블을 포함하면 다른 데이터 열과 마찬가지로 계산된 열을 사용할 수 있습니다.

계산 열의 수식은 Excel에서 만드는 수식과 매우 유사합니다. 그러나 Excel과 달리 테이블의 다른 행에 대해 다른 수식을 만들 수는 없습니다. 대신 DAX 수식이 전체 열에 자동으로 적용됩니다.

열에 수식이 있으면 각 행에 대해 값이 계산됩니다. 수식을 만드는 즉시 열에 대한 결과가 계산됩니다. 열 값은 기본 데이터를 새로 고치거나 수동 다시 계산을 사용하는 경우에만 다시 계산됩니다.

측정값과 다른 계산된 열을 기반으로 하는 계산된 열을 만들 수 있습니다. 그러나 계산된 열과 측정값에 같은 이름을 사용하는 경우 혼동스러운 결과가 발생할 수 있으므로 피해야 합니다. 열을 참조할 때는 실수로 측정값을 호출하지 않도록 정규화된 열 참조를 사용하는 것이 가장 좋습니다.

자세한 내용은 Power Pivot의 계산된 열을 참조하세요.

측정값

측정값은 파워 피벗 데이터를 사용하는 피벗 테이블(또는 피벗 차트)에서 사용하기 위해 특별히 만들어진 수식입니다. 측정값은 COUNT 또는 SUM과 같은 표준 집계 함수를 기반으로 하거나 DAX를 사용하여 고유한 수식을 정의할 수 있습니다. 측정값은 피벗 테이블의 값 영역에서 사용됩니다. 계산된 결과를 피벗 테이블의 다른 영역에 배치하려면 대신 계산된 열을 사용합니다.

명시적 측정값에 대한 수식을 정의하는 경우 측정값을 피벗 테이블에 추가하기 전까지는 아무 작업도 수행하지 않습니다. 측정값을 추가하면 피벗 테이블의 값 영역에 있는 각 셀에 대해 수식이 계산됩니다. 각 행과 열 머리글의 조합에 대해 결과가 만들어지므로 측정값에 대한 결과는 각 셀에서 다를 수 있습니다.

만드는 측정값의 정의는 원본 데이터 테이블과 함께 저장됩니다. 피벗 테이블 필드 목록에 나타나며 통합 문서의 모든 사용자가 사용할 수 있습니다.

자세한 내용은 Power Pivot의 측정값을 참조하세요.

수식 입력줄을 사용하여 수식 만들기

파워 피벗은 Excel과 마찬가지로 수식을 더 쉽게 만들고 편집할 수 있도록 수식 입력줄을 제공하고 자동 완성 기능을 제공하여 입력 및 구문 오류를 최소화합니다.

테이블의 이름을 입력하려면 테이블 이름을 입력하기 시작합니다. 수식 자동 완성은 해당 문자로 시작하는 유효한 이름이 포함된 드롭다운 목록을 제공합니다.

열 이름을 입력하려면 대괄호를 입력한 다음 현재 테이블의 열 목록에서 열을 선택합니다. 다른 테이블의 열에 대해 테이블 이름의 첫 문자를 입력한 다음 자동 완성 드롭다운 목록에서 열을 선택합니다.

수식을 작성하는 방법에 대한 자세한 내용과 연습은 Power Pivot에서 계산용 수식 만들기를 참조하세요.

자동 완성 사용 팁

중첩 함수가 있는 기존 수식 중간에 수식 자동 완성을 사용할 수 있습니다. 삽입 지점 바로 앞의 텍스트는 드롭다운 목록의 값을 표시하는 데 사용되며 삽입 지점 뒤의 모든 텍스트는 변경되지 않은 상태로 유지됩니다.

상수에 대해 만든 정의된 이름은 자동 완성 드롭다운 목록에 표시되지 않지만 계속 입력할 수는 있습니다.

파워 피벗은 함수의 닫는 괄호를 추가하거나 자동으로 괄호를 일치시키지 않습니다. 각 함수의 구문이 올바른지 확인해야 하며, 그렇지 않으면 수식을 저장하거나 사용할 수 없습니다. 

수식에서 여러 함수 사용

함수를 중첩할 수 있습니다. 즉, 한 함수의 결과를 다른 함수의 인수로 사용합니다. 계산된 열에는 최대 64개 수준의 함수를 중첩할 수 있습니다. 그러나 중첩으로 인해 수식을 만들거나 문제를 해결하기 어려울 수 있습니다.

많은 DAX 함수는 중첩 함수로만 사용하도록 설계되었습니다. 이러한 함수는 테이블을 반환하며, 테이블은 결과로 직접 저장할 수 없습니다. 테이블 함수에 대한 입력으로 제공되어야 합니다. 예를 들어 함수 SUMX, AVERAGEX 및 MINX에는 모두 첫 번째 인수로 테이블이 필요합니다.

DAX 함수와 Excel 함수 비교

DAX 함수 라이브러리는 Excel 함수 라이브러리를 기반으로 하지만, 라이브러리마다 차이점이 많습니다. 이 섹션에서는 Excel 함수와 DAX 함수의 차이점과 유사점을 요약합니다.

• 많은 DAX 함수는 Excel 함수와 이름이 같고 일반적인 동작이 동일하지만 다른 유형의 입력을 받도록 수정되었으며 경우에 따라 다른 데이터 형식을 반환할 수 있습니다. 일반적으로 약간의 수정 없이는 Excel 수식에서 DAX 함수를 사용하거나 Power Pivot에서 Excel 수식을 사용할 수 없습니다.
• DAX 함수는 셀 참조나 범위를 참조로 사용하지 않고, 대신 DAX 함수는 열이나 테이블을 참조로 사용합니다.
• DAX 날짜 및 시간 함수는 날짜/시간 데이터 형식을 반환합니다. 반면 Excel 날짜 및 시간 함수는 날짜를 일련 번호로 나타내는 정수를 반환합니다.
• 많은 새 DAX 함수는 값 테이블을 반환하거나 값 테이블을 입력으로 기반으로 계산을 수행합니다. 반면 Excel에는 표를 반환하는 함수가 없지만 일부 함수는 배열을 사용할 수 있습니다. 전체 테이블과 열을 쉽게 참조하는 기능은 Power Pivot의 새로운 기능입니다.
• DAX는 Excel의 배열 및 벡터 조회 함수와 유사한 새 조회 함수를 제공합니다. 그러나 DAX 함수를 사용하려면 테이블 간에 관계가 설정되어야 합니다.
• 열의 데이터는 항상 동일한 데이터 형식이어야 합니다. 데이터가 동일한 형식이 아닌 경우 DAX는 전체 열을 모든 값을 가장 잘 수용하는 데이터 형식으로 변경합니다.

DAX 데이터 형식

다양한 데이터 형식을 지원할 수 있는 여러 데이터 원본에서 데이터를 Power Pivot 데이터 모델로 가져올 수 있습니다. 데이터를 가져오거나 로드한 다음 계산이나 피벗 테이블에서 데이터를 사용하면 데이터가 파워 피벗 데이터 형식 중 하나로 변환됩니다. 데이터 형식 목록은 데이터 모델의 데이터 형식을 참조하세요.

테이블 데이터 형식은 DAX의 새로운 데이터 형식으로, 여러 새 함수에 대한 입력 또는 출력으로 사용됩니다. 예를 들어 FILTER 함수는 테이블을 입력으로 사용하고 필터 조건을 충족하는 행만 포함하는 다른 테이블을 출력합니다. 테이블 함수와 집계 함수를 결합하면 동적으로 정의된 데이터 집합에 대해 복잡한 계산을 수행할 수 있습니다. 자세한 내용은 Power Pivot의 집계를 참조하세요.

수식 및 관계형 모델

파워 피벗 창은 여러 데이터 테이블로 작업하고 관계형 모델의 테이블을 연결할 수 있는 영역입니다. 이 데이터 모델 내에서 테이블은 관계를 통해 서로 연결되므로 다른 테이블의 열과 상관 관계를 만들고 더 흥미로운 계산을 만들 수 있습니다. 예를 들어 관련 테이블의 값을 합산하는 수식을 만든 다음 해당 값을 단일 셀에 저장할 수 있습니다. 또는 관련 테이블의 행을 제어하기 위해 테이블과 열에 필터를 적용할 수 있습니다. 자세한 내용은 데이터 모델의 테이블 간 관계를 참조하십시오.

관계를 사용하여 테이블을 연결할 수 있으므로 피벗 테이블에 서로 다른 테이블에 있는 여러 열의 데이터를 포함할 수도 있습니다.

그러나 수식은 전체 테이블과 열에서 사용할 수 있으므로 Excel에서와는 다르게 계산을 디자인해야 합니다.

• 일반적으로 열의 DAX 수식은 항상 열의 전체 값 집합에 적용됩니다(일부 행이나 셀에만 적용되지 않음).
• Power Pivot의 테이블은 각 행의 열 수가 항상 동일해야 하며 열의 모든 행에 동일한 데이터 형식이 포함되어야 합니다.
• 테이블이 관계로 연결된 경우 키로 사용되는 두 열의 값이 대부분 일치하는지 확인해야 합니다. Power Pivot에서는 참조 무결성을 적용하지 않으므로 키 열에 일치하지 않는 값이 있더라도 관계를 만들 수 있습니다. 그러나 값이 비어 있거나 일치하지 않는 값이 있으면 수식 결과와 피벗 테이블 모양에 영향을 줄 수 있습니다. 자세한 내용은 Power Pivot 수식의 조회를 참조하세요.
• 관계를 사용하여 테이블을 연결하면 수식이 계산되는 scope 또는 컨텍스트가 확대됩니다. 예를 들어 피벗 테이블의 수식은 피벗 테이블의 필터나 열, 행 머리글의 영향을 받을 수 있습니다. 컨텍스트를 조작하는 수식을 작성할 수 있지만, 컨텍스트로 인해 예상하지 못한 방식으로 결과가 변경될 수도 있습니다. 자세한 내용은 DAX 수식의 컨텍스트를 참조하세요.

수식 결과 업데이트

데이터 새로 고침 및 다시 계산은 복잡한 수식, 대량의 데이터 또는 외부 데이터 원본에서 가져온 데이터가 포함된 데이터 모델을 디자인할 때 이해해야 하는 별도의 두 관련 작업입니다.

데이터 새로 고침은 통합 문서의 데이터를 외부 데이터 원본의 새 데이터로 업데이트하는 프로세스입니다. 지정한 간격으로 데이터를 수동으로 새로 고칠 수 있습니다. 또는 통합 문서를 SharePoint 사이트에 게시한 경우 외부 원본에서 자동 새로 고침을 예약할 수 있습니다.

다시 계산 은 수식 자체의 변경 내용을 반영하고 이러한 변경 내용을 기본 데이터에 반영하기 위해 수식 결과를 업데이트하는 프로세스입니다. 다시 계산은 다음과 같은 방식으로 성능에 영향을 줄 수 있습니다.

• 계산된 열의 경우 수식을 변경할 때마다 항상 전체 열에 대한 수식 결과가 다시 계산됩니다.
• 측정값의 경우 측정값을 피벗 테이블 또는 피벗 차트의 컨텍스트에 배치할 때까지 수식의 결과가 계산되지 않습니다. 데이터의 필터에 영향을 주는 행 또는 열 머리글을 변경하거나 피벗 테이블을 수동으로 새로 고치는 경우에도 수식이 다시 계산됩니다.

수식 문제 해결

수식 작성 시 오류

수식을 정의할 때 오류가 발생하면 수식에 구문 오류, 의미 체계 오류 또는 계산 오류가 있을 수 있습니다.

구문 오류는 가장 쉽게 resolve할 수 있습니다. 일반적으로 누락된 괄호나 쉼표가 포함됩니다. 개별 함수 구문에 대한 도움말은 DAX 함수 참조를 참조하세요.

다른 유형의 오류는 구문은 올바르지만 참조하는 값이나 열이 수식의 컨텍스트에서 의미가 없는 경우에 발생합니다. 이러한 의미 체계 및 계산 오류는 다음 문제로 인해 발생할 수 있습니다.

• 수식이 존재하지 않는 열, 테이블 또는 함수를 참조합니다.
• 수식이 올바른 것처럼 보이지만 데이터 엔진이 데이터를 가져올 때 형식 불일치를 발견하고 오류를 발생합니다.
• 수식이 함수에 전달하는 매개 변수의 개수나 유형이 잘못되었습니다.
• 수식이 오류가 있는 다른 열을 참조하므로 해당 값이 잘못되었습니다.
• 수식은 처리되지 않은 열을 참조합니다. 즉, 메타데이터는 있지만 계산에 사용할 실제 데이터는 없습니다.

처음 네 가지 경우 DAX는 잘못된 수식이 포함된 전체 열에 플래그를 지정합니다. 마지막 경우 DAX는 열을 회색으로 표시하여 열이 처리되지 않은 상태임을 나타냅니다.

열 값의 순위를 지정하거나 정렬할 때 올바르지 않거나 비정상적인 결과가 나타남

NaN(숫자가 아님) 값을 포함하는 열의 순위를 매기거나 순서를 지정할 때 잘못된 결과나 예기치 않은 결과가 나올 수 있습니다. 예를 들어 계산에서 0을 0으로 나누면 NaN 결과가 반환됩니다.

이는 수식 엔진이 숫자 값을 비교하여 순서 지정 및 순위 지정을 수행하기 때문입니다. 그러나 NaN은 열의 다른 숫자와 비교할 수 없습니다.

올바른 결과를 보장하려면 IF 함수를 사용하는 조건문을 사용하여 NaN 값을 테스트하고 숫자 0 값을 반환할 수 있습니다.

Analysis Services 테이블 형식 모델 및 DirectQuery 모드와의 호환성

일반적으로 파워 피벗에서 작성하는 DAX 수식은 Analysis Services 테이블 형식 모델과 완벽하게 호환됩니다. 그러나 Power Pivot 모델을 Analysis Services instance로 마이그레이션한 다음 DirectQuery 모드로 모델을 배포하는 경우 몇 가지 제한 사항이 있습니다.

• DirectQuery 모드에서 모델을 배포하는 경우 일부 DAX 수식이 다른 결과를 반환할 수 있습니다.
• 관계형 데이터 원본에 대해 지원되지 않는 DAX 함수가 수식에 포함되어 있기 때문에 일부 수식은 모델을 DirectQuery 모드로 배포할 때 유효성 검사 오류를 일으킬 수 있습니다.

자세한 내용은 SQL Server 2012 온라인 설명서의 Analysis Services 테이블 형식 모델링 설명서를 참조하세요.