史上第一份基於DAX數據模型編寫的美國人口遷徙地圖

今天向大家分享一則基於power pivot數據模型的複雜數據地圖.

此則地圖表達的是人口的遷徙,紅色圓邊框紅表示Leaving.如果點擊切片器上的Leaving,只在在地圖上紅色圓邊框紅.紅色圓點表示為Arriving.,如果點擊切片器上的Arriving,只能看到圓點,選擇Net,表示為凈值,紅色邊框線與紅色圓點也就都能看到了.點擊不同的城市,圖中的氣泡能隨著地圖上的放射線移動,

畫完這張地圖,心裡特別高興,居然用DAX公式編寫出來了複雜的數據地圖.用DAX公式編寫地圖的優點就是數據點的坐標值都是放在維度表上的,因此事實表上的數據更新到幾百萬行,依然還可以使用.更重要的一點是,公式由於處於DAX數據模型層面編寫的,可以更自由的做複雜篩選,增加更多的數據切片.缺點就是比工作表函數要更難理解與編寫, 難的原因有很多,重要的一條就是普通excel用戶早已習慣了excel的函數工作原理,它的工作原理就是通過橫坐標,縱坐標確定單元格的位置來進行編寫函數公式,,而power pivot是一個數據倉庫,它的數據存儲方式與公式的編寫更像資料庫,.如果你沒有學習過一點關於DAX數據建模的知識,恐怕我下面寫的文字,會有點不知所云..

製圖原理:

從製圖原理上,是兩張圖表疊加在一起,一張用來做氣泡圖,別一張繪製帶點的散點圖,形成射線狀.

如圖是泡泡圖的數據源,副Y,副X是城市的坐標軸,切片器選擇Teaving,左邊Arr列一律為0,選擇Arriving,右邊的Arr列一律為0,選擇Net,兩列數據同是不為0.於是在圖表上呈現不同的效果.

再用另外的帶點的散點圖構造放射性線條.

觀察數據,會發現城市是有規律的錯行,於是以出發城市Wisconsin為起點向各個城市呈現放射狀的線條.

公式編寫:

製圖原理大概清楚了,剩下的就是公式的編寫與模型的構造,這也是最難的.

如上圖所示,是標準的一維數據,有一萬多行,每一行數據表達了兩個城市人口遷徙的關係,狀態為Arriving表示到達.,Leaving表示離開

為了獲取兩個城市之間的坐標,必須再另外構造兩個城市坐標的維度表.

兩個維度表的數據是一模一樣的,只是出發地城市坐標這張表的satae計算列的關係線對應數據源的出發地計算列,,而目的地城市坐標這張表的的satae計算列的關係線對應數據源的到達地計算列.別外一張表切片器,不需要關係,是到時用來做切片選擇的.

由於兩個城市列的對應該坐標值是數值類型,放入透視表值區域會自動進行求和,這樣的坐標數據肯定是錯誤的,不需要.如何防止?那必須寫如下公式:

X坐標:=MINX(TOPN(1,數據源,[出發地],DESC),RELATED(出發地城市坐標[lng]))

用TOPN函數可以有效的防止坐標軸在放入值區域時進行累加求和.再嵌套MINX迭代函數只取坐標軸這一列數據.

同樣的公式,用來獲取出發地的Y坐標,以及到達地這列的XY坐標

Y坐標:=MINX(TOPN(1,數據源,[出發地],DESC),RELATED(出發地城市坐標[lat]))

副X:=MINX(TOPN(1,數據源,[出發地],DESC),RELATED(目的地城市坐標[lng]))

副Y:=MINX(TOPN(1,數據源,[出發地],DESC),RELATED(目的地城市坐標[lat]))

依然用相同的公式用來獲取對應的城市名字,這樣就可以將文本類型的城市名字放進數據透視表的值區域.

出發city:=MINX(TOPN(1,數據源,[出發地],DESC),[出發地])

無論數據怎麼改變,永遠取最大的數值,這個數值將來是用於固定氣泡的大小,公式簡單.

最大值:=CALCULATE(max(數據源[數值]),ALL(數據源))

作用於切片器

, 的公式,

度量值 1:=if(HASONEVALUE(切片器[狀態選擇]),CALCULATE(SUM(數據源[數值]),數據源[狀態]=VALUES(切片器[狀態選擇])),0)

HANONEVALUE是典型的作用於切片器選擇的函數,當切片器選擇一個數值時,返回trun,選擇多個返回false..這個公式用來過渡的,為了構造如下兩個公式的一個子變數.

Arr:=if(CALCULATE(SUM(數據源[數值]),數據源[狀態]="Arriving")=[度量值 1]||VALUES(切片器[狀態選擇])="Net",CALCULATE(SUM(數據源[數值]),數據源[狀態]="Arriving"),0)

Leav:=if(CALCULATE(SUM(數據源[數值]),數據源[狀態]="Leaving")=[度量值 1]||VALUES(切片器[狀態選擇])="Net",CALCULATE(SUM(數據源[數值]),數據源[狀態]="Leaving"),0)

兩個公式比較費腦,完全是作用於切片器的,當切片器選擇為Arriving或Net時,Arr欄位在透視表中就呈現城市對應的數值,否則返回0,.或者當切片器選擇為Leaving或Net時,Leav欄位就在透視表中呈現城市對應的數值否則返回0.

用來獲取兩個城市數值間的差值.比如從A城市到達B城市的人口數值是15,從A城市離開到達B城市的人口數值是20,那差值無非是20-15=5.但在power pivot里很難進行這樣的簡單運算,它是標準的資料庫存儲的形式,是兩行不同的信息,很難並排放在一行進行計算.所以下面這個公式就同樣非常費腦了,構造一個虛擬表,將兩行不同的信息放在同一行進行相減.

差值:=MINX(ADDCOLUMNS(VALUES(數據源[合併]),"差值1",CALCULATE(SUM(數據源[數值]),數據源[狀態]="Arriving")-CALCULATE(SUM(數據源[數值]),數據源[狀態]="Leaving")),[差值1])

上面公式寫完,只要拖放進數據透視表,已經差不多可以正常出圖.最後將一張美國地圖放在圖表的最底層就可以了.只是射線地圖還有一點缺失,必須稍微利用一下工作表函數.以後想辦法彌補.具體的內容可以參考原文件. 估計是世界上第一份基於power pivot數據模型弄出來的複雜散點地圖圖表.編寫DAX公式的靈感來自於劉凱老師

在此致謝!

DAX表達式編輯了一份中國地圖