DAX와 파워비아이

파워쿼리와 엑셀의 차이점은 많이 있습니다.

수식의 입력이나 함수의 사용이나 뭐 하나 같은 것이 없습니다.

하지만 가장 큰 차이점은 '단위의 구성'입니다.

앞에서도 간단히 설명해 드렸지만 이에 대한 이해가 없다면 파워쿼리로 작업 시 많은 혼동을 가지고 옵니다.

​

엑셀을 잠시 보겠습니다.

빨간색 박스가 보이죠?

엑셀은 Cell이라는 단위로 구성이 되어있습니다.

셀 단위의 구성으로 인해 좀 더 독립적이고 자유롭게 이용을 할 수 있습니다.

즉 A3, C4, C6는 독립적인 단위이고, C7=A3*(C4+C6)라는 수식을 이용한다면 셀 C7에 수식의 결과치를 나타냅니다.

또 다른 셀에 숫자나 문자를 입력해 또 다른 수식을 만들 수 있습니다.

​

반면 쿼리는 어떨까요?

쿼리는 엑셀만큼 독립적이지는 않습니다.

쿼리에는 Cell의 개념이 없고 열의 개념을 가지고 있습니다.

(참고로 지금 빨간색 박스로 표시한 것은 열이라고 이야기를 합니다. 영문 버전은 Column이라고 표시를 하는데 약간 헷갈리네요)

파워쿼리는 열이라는 단위로 구성이 되어있기 때문에 열 전체가 한 덩어리로 움직입니다.

엑셀처럼 A3* A4라는 애초에 불가능하다는 이야기입니다. (물론 완전히 불가능하다는 이야기는 아니지만 이런 수식을 만들기 위해서는 많은 작업이 필요합니다)

​

열의 이름으로 단위가 구성되며 곱셈을 원한다면 [Sun]*[Mon]이라는 수식이 성립됩니다.

그럼 자동으로 [Sun]의 1행과 [Mon]의 1행이 곱해져 결과치를 보여주게 됩니다.

​

여기서 가장 중요한 것 중의 하나가 바로 열의 속성인데 위를 보시면 [Date]라는 열은 속성이 날짜이며, 나머지 열들은 속성이 숫자로 되어있습니다.

만일 숫자로 속성이 되어있는 열의 중간에 다른 속성 (날짜, 혹은 글자)이 있다면 Error로 표시가 됩니다.

그리고 이 Error를 수정하지 않는 한 다음 작업도 진행이 되지 않습니다.

왜 그럴까 하는 생각도 들지만 방대한 분량의 데이터를 취급하기 위한 최선의 방법이 아닐까 합니다.

​

이야기가 길어질 것 같으니 여기서는 차이점만 간단히 말씀드리겠습니다.

​

파워쿼리를 시작할 때 가장 먼저 잡아야 할 개념은 열 단위로 구성이 된다는 것입니다.

그리고 계산이 이루어질 경우 열의 같은 행끼리 이루어지며 (예를 들어 [Mon]+[Tue]라고 수식을 입력한다면 1행은 1행끼리, 2행은 2행끼리 자동으로 수식이 진행됩니다) 2행과 3행으로 크로스 되어서 진행이 되지 않은 다는 것입니다.

​

엑셀처럼 자유롭게 한 열에 여러 수식을 사용할 수도 없으며, 반드시 정해진 방법에 따라 열을 기본 단위로 해서 모든 수식이나 열의 추가가 이루어진다는 것을 꼭 염두에 두시고 시작을 하셨으면 합니다.

​

처음 접하시는 분들은 약간 혼동이 올 수도 있겠지만, 어느 정도 익숙해지면 데이터를 변환하는데 이만한 툴은 없을 거라 생각을 하시게 될 것입니다.

VLOOKUP 함수도 엑셀에서 많이 사용되는 함수중 하나라 생각됩니다.

다른 시트에 있는 여러 데이터를 쉽게 가지고 올 수 있는 함수입니다.

엑셀에 관한 설명은 넘어가기로 하고, 아래를 먼저 보겠습니다.

먼저 2개의 테이블이 있다고 가정하겠습니다.

하나는 모든 벽의 종류와 각 벽마다 몇 겹의 드라이월 보드가 들어가는지를 보여주는 테이블입니다.

다른 하나는 각 층별 벽의 종류와 각 벽의 수량으로 이루어진 테이블입니다.

여기서 드라이월 보드의 총면적을 구하려면 Quantity * Drywall이라는 수식이 필요하겠죠

​

엑셀이라면 VLOOKUP 함수를 사용해서 가져오면 되겠지만, Power BI에서는 VLOOKUP 함수가 존재하지 않습니다.

역시 비슷한 개념을 가진 LOOKUPVALUE라는 함수를 하용해야 하는데요

첫 번째 테이블 쿼리의 이름은 'Type'이고 두 번째 테이블 쿼리의 이름은 'Quantity'입니다.

그럼 Type 쿼리의 Drywall열의 숫자를 가지고 오는데 Type 쿼리의 Type열과 Quantity 쿼리의 Type열이 일치가 된 행의 값만 가지고 오면 되겠죠?

수식으로 나타내면 다음과 같습니다.

위의 수식을 잠시 설명하자면

Drywall이라는 열을 만드는데 'Type'[Type]과 Quantity[Type]가 일치를 할 경우 'Type'[Drywall]의 값을 가지고 오라는 수식이 되겠습니다.

엑셀의 VLOOKUP 함수와 입력방법이 약간 다르지만 크게 어렵지는 않다고 생각합니다.

​

여기서 전체 Drywall Board의 수량을 구하려면 Quantity열에 Drywall열을 곱하면 나오겠죠?

여기서는 SUMPRODUCT와 비슷한 개념을 가진 SUMX를 사용하겠습니다.

SUMX를 사용하여 Drywall Board의 총면적을 구했습니다.

SUM을 사용하지 않고 SUMX를 사용한 이유는 아래의 Total을 보면 아실 것입니다.

11071*68은 32679가 아닙니다.

왜 그런가는 전에 설명드린 SUM과 SUMX 함수의 차이점을 참조하시길 바랍니다.

​

Total Drywall의 합계를 보여주기 위해 SUM 함수 대신 SUMX 함수를 하용하였으며 아주 간단히 두 테이블을 이용해 Drywall Board의 면적을 계산하였습니다.

​

파워비아이 에서는 LOOKUPVALUE 함수가 엑셀의 VLOOKUP 함수와 똑같은 기능을 한다는 것과, LOOKUPVALUE의 입력방법을 알아두신다면 아주 유용하게 사용될 거라 생각됩니다.

저번 글에서 DAX에는 SUMIF함수가 없다고 말씀을 드렸습니다.

마찬가지로 COUNTIF함수도 존재를 하지 않습니다.

하지만 다른 함수들을 이용해 COUNTIF함수와 똑같은 기능을 가지도록 만들 수 있습니다.

​

아래는 Material이라는 이름을 가진 쿼리이며 각 층별, 타일의 형태별 개수를 보여줍니다.

먼저 COUNT 함수를 사용한다면 행의 개수를 반환하므로 15가 나와야겠죠?

FLOOR열을 기준으로 COUNT함수를 이용해 행의 개수를 구하겠습니다.

Material 쿼리에 있는 [Floor]열의 개수를 반환하라는 함수입니다.

​

그럼 이제 엑셀에서 자주 쓰는 COUNTIF와 똑같은 역할을 하는 함수를 이용할 건데요.

예전에는 CALCULATE 함수 안에 FILTER를 이용해 구했었는데, COUNTX라는 함수를 알게 된 뒤로는 COUNTX만 사용하고 있습니다.

COUNTX는 함수 안에 FILTER를 삽입하여 조건을 만들어줄 수 있습니다.

여기서도 Material 쿼리 내에 있는 [Floor]열의 개수를 구하지만, FILTER를 삽입해 Materila 쿼리의 [Tile]이라는 열에서 "Type A"에 해당되는 행만 카운트하라는 함수가 되겠습니다.

​

그럼 1층에 Type A 만 카운트하려면 어떻게 해야 할까요?

필터가 2개가 필요하겠죠

여기서는 AND를 이용하면 되는데 간단히 &&를 삽입하는 것 만으로 해결이 될 수 있습니다.

편의상 이름을 COUNTXs라고 하겠습니다.

Material[Tile]="Type A" && Material[Floor]="1st"

FILTER 중간에 이렇게 &&를 넣어주면 AND라는 연산자처럼 사용할 수 있으며 [Tile] 열의 "Type A"와 [Floor] 열의 "1st" 에 해당되는 행만 카운트하라는 의미가 되면 결과는 당연히 1이 되겠죠

​

마지막으로 제가 COUNTX라는 함수를 알기 전에 주로 사용하던 함수를 사용하겠습니다.

결과는 COUNTX를 사용할 때와 똑같습니다.

똑같이 필터를 사용했으며 다른 것 하나는 CALCULATE 함수 안에 COUNTROWS를 사용한 것이죠

COUNTROWS는 쿼리 안의 모든 행의 개수를 카운트하는 것이고, 여기에 FILTER를 이용하면 똑같은 결과를 얻을 수 있습니다.

​

행의 개수를 알려주는 COUNT함수에 대해 알아봤는데, 목적지로 운전을 해서 갈 때 여러 가지의 길이 있듯이 똑같은 결과치를 보여주더라도 함수의 사용에 따라 여러가지의 수식이 존재합니다.

딱히 정답이 있는 건 아니므로 각자가 편한 방법을 사용하면 좋을 것이라 생각합니다.

​

하지만 이전의 글과 같이 파워 비아이는 슬라이서를 이용해 필터링 작업을 하는 경우가 많아서 특별한 경우를 제외하고는 그렇게 많이 사용하지 않았던 것 같습니다.

​

하지만 만일 뭔가를 콕 집어서 보여줘야 할 필요가 있다면 알아두면 유용한 수식이 될 거라 생각합니다.

DAX에서 SUMIF함수는 존재하지 않습니다.

사실 보고서에서 슬라이서로 클릭만 하면 원하는 결과를 보여주기 때문에 딱히 필요가 없는 함수 일지 모릅니다.

​

아래와 같은 쿼리가 있다고 가정을 하겠습니다.

여기서 Zone A만의 합계를 구한다고 생각을 하면 엑셀에서는 SUMIF를 사용해서 A의 합계만 계산할 수 있습니다.

하지만 아래와 같이 Power BI에서는 슬라이서를 이용하면 간단히 해결이 될 수 있기 때문에 크게 문제가 되지 않았던 함수였는것 같습니다.

위에 보여지는 슬라이서에서 A를 클릭하면 딱히 수식을 이용하지 않더라도 Zone A의 합계가 자동 계산이 되어 카드로 표시가 되고, 1F를 선택하게 되면 자동으로 1F의 합계를 보여주게 되죠.

​

하지만 어떤 경우는 슬라이서를 사용하지 않고 계산을 해야하는 경우가 생길 수도 있죠.

이럴 때는 계산을 해야 하는데 DAX에서는 SUMIF함수가 존재하지 않는다라는 이야기는 처음에 했습니다.

DAX에서는 CALCULATE함수를 이용하면 해결이 가능합니다.

​

712는 Process의 합을 보여주고, 아래에 149는 CALCULATE함수를 이용해 Zone A만의 합산을 구한 것입니다.

간단히 정리를 하자면 FILTER함수로 Wall 쿼리의 Zone이라는 열에서 "A"만 추출한 Wall 쿼리의 Process열의 합산을 구하라는 식이 되겠습니다.

그럼 조금 전에 슬라이서로 A를 선택했을 때와 똑같은 값은 보여줍니다.

그리고 FILTER는 여러 번 반복해서 사용이 될 수 있습니다.

만약 1F에 있는 Zone A의 합계만 보고 싶다면 아래와 같이 필터를 하나 더 추가하면 됩니다.

SUMIFS = CALCULATE(SUM(Wall[Process]),FILTER(WALL,WALL[Zone]="A"),FILTER(Wall,Wall[Floor]="1F"))

그럼 엑셀에서 SUMIFS와 같은 수식이 만들어집니다.

​

간단히 DAX에서 SUMIF를 계산할 수 있는 CALCULATE함수에 대해 알아보았습니다.

CALCULATE내에는 꼭 SUM이 아니라 다른 함수도 쓰일 수 있기에 여러 함수를 이용해서 수식을 만들어 보는 것도 재미있는 일이라 생각합니다.

엑셀 함수에서 가장 많이 사용되는 함수중 하나가 IF함수가 아닐까 합니다.

DAX에서의 IF함수의 형태는 100% 동일합니다.

딱 하나 다른 점은 셀 대신 열의 이름을 입력해야 한다는 것입니다.

​

IF(조건, 참의 값, 거짓의 값)으로 똑같지만 이전 글에 설명을 드렸듯이 엑셀은 셀 단위로 구성이 되고 쿼리는 열 단위로 구성이 되기 때문에 이것만 다르고 다른 것은 100% 동일합니다.

​

엑셀에서는 IF(C3>500,"높음","낮음")으로 구성이 된다면 DAX에서는 IF('TABLE'[SALES]>500,"높음","낮음")으로 구성이 됩니다.

쿼리의 이름은 ''으로 표시가 되면 열의 이름은 []으로 표시가 됩니다.

그래서 'TABLE'은 쿼리의 이름이며, [SALES]는 쿼리 내 열의 이름이 됩니다.

다시 말해서 TBALE 쿼리 내 SALES라는 열이 500보다 크다면 "높음"으로 표시하고 그렇지 않다면 "낮음"으로 표시해라는 수식이 되는 겁니다.

​

DAX에는 엑셀과 다른 여러 가지 함수가 존재하지만 이처럼 엑셀과 100% 일치하는 수식도 많습니다.

​

이번 글부터는 엑셀에서 많이 사용되는 함수를 위주로 DAX함수와의 차이점에 대해 설명하겠습니다.