Power BI:M与DAX以及度量与计算列
When I embarked on my Power BI journey I was almost immediately slapped with an onslaught of foreign and perplexing terms that all seemed to do similar, but somehow different, things.
當(dāng)我開始Power BI之旅時(shí),我?guī)缀趿⒖淌艿搅送鈬屠Щ笮g(shù)語的沖擊,這些術(shù)語似乎都在做著相似但有所不同的事情。
There is a language called M but there is also a language called DAX? What is the difference between a calculated column and a measure? When should I be using Power Query?
T 這里是一種叫做M的語言,但也有一種叫做DAX的語言? 計(jì)算列和度量之間有什么區(qū)別? 什么時(shí)候應(yīng)該使用Power Query?
Photo by Daniel Jensen on Unsplash Daniel Jensen在Unsplash上拍攝的照片I got stuck really early in my journey trying (and failing) to understand some key concepts that made Power BI seem much more difficult and confusing than it is. I almost abandoned my BI ambitions to crawl back to my familiar pal, Excel. Better the devil you know, right? WRONG!
我在嘗試(失敗)嘗試的過程中很早就陷入了困境,并且未能理解一些使Power BI看起來比實(shí)際困難和混亂的關(guān)鍵概念。 我?guī)缀醴艞壛宋业腂I野心,回到了我熟悉的朋友Excel。 更好的惡魔,對(duì)嗎? 錯(cuò)誤!
I am so, so, SO happy that I kept banging my head against the wall to get over those initial hurdles. Today, I use Power BI almost daily, and I use it cheerfully, gleefully even, and I rarely use Excel. But getting here was hard, really hard. And it was harder than it should have been because, for a long time, I did not have a clear, conceptual understanding of some key Power BI concepts.
我是如此,如此,如此高興,我一直將頭撞在墻上以克服那些最初的障礙。 今天,我?guī)缀趺刻於荚谑褂肞ower BI,并且愉快,愉快地使用它,而我很少使用Excel。 但是到這里很難,真的很難。 而且這比原本應(yīng)該的困難,因?yàn)楹荛L一段時(shí)間以來,我對(duì)某些關(guān)鍵的Power BI概念沒有清晰的概念性理解。
I am going to try to keep things as simple as possible to give you just a hint, a tinge, a taste to get you started.
我將嘗試使事情盡可能簡單,以便給您一個(gè)提示 , 淡淡的色彩和使您入門的品味 。
More experienced Power BI users might cringe at the way I am (over) simplifying and reducing these concepts, but you know what? At this point, all of the nuance and minutia really doesn’t matter. You need a place to start. Trust me, the complexity will come.
經(jīng)驗(yàn)豐富的Power BI用戶可能會(huì)對(duì)我簡化和減少這些概念的方式感到畏縮,但是您知道嗎? 在這一點(diǎn)上,所有細(xì)微差別和細(xì)節(jié)都不重要。 您需要一個(gè)起點(diǎn)。 相信我,復(fù)雜性將會(huì)到來。
So, my intrepid warrior, my Padawan, my Power BI master-to-be, here is a very basic overview of:
因此,我的勇敢勇士,Padawan和Power BI未來的主人,這里是以下內(nèi)容的非常基本的概述:
Power Query and M
功率查詢和M
DAX
達(dá)克斯
Calculated Column and Measures
計(jì)算列和度量
Power Query vs. DAX
電源查詢與DAX
功率查詢和M (Power Query and M)
What the heck is it? This is where it all begins. Power Query is where you pull your data into Power BI. M is the coding language used by Powery Query. You can use Power Query by pointing and clicking and the code in M will essentially be created for you. You can also write your own code in M directly.
這到底是什么? 這就是一切的開始。 Power Query是將數(shù)據(jù)拉入Power BI的地方。 M是Powery Query使用的編碼語言。 您可以通過指向和單擊來使用Power Query,M中的代碼實(shí)際上將為您創(chuàng)建。 您也可以直接在M中編寫自己的代碼。
When do I use it? To clean and format your data. You can do things like remove and add columns, filter out data, change column formatting, etc.
什么時(shí)候使用? 清理和格式化數(shù)據(jù)。 您可以執(zhí)行刪除和添加列,過濾數(shù)據(jù),更改列格式等操作。
Do I have to use M?: Short answer, no. You can do most things in Power Query by pointing and clicking without needing to use M at all. However, knowing M can be really helpful in making your process more flexible and easier to replicate in the future. For example, when using M you can copy and paste bits of code you want to reuse and you can annotate your steps.
我必須使用M嗎? :簡短回答,不。 您可以通過指向和單擊來執(zhí)行Power Query中的大多數(shù)操作,而根本不需要使用M。 但是,了解M確實(shí)可以使您的過程更靈活,將來更容易復(fù)制。 例如,使用M時(shí),您可以復(fù)制和粘貼要重用的代碼位,并可以注釋步驟。
Tip for learning: Do what you need to in Power Query by pointing and clicking and then open the Advanced Editor. The M code will be there for the manipulations you have just completed. This way, you can start to get familiar with the language and by making small tweaks before you try writing your own M code from scratch.
學(xué)習(xí)提示 :指向并單擊,然后打開“ 高級(jí)編輯器” ,即可執(zhí)行Power Query中需要執(zhí)行的操作 。 M代碼將在其中用于您剛剛完成的操作。 這樣,在嘗試從頭開始編寫自己的M代碼之前,您可以進(jìn)行一些細(xì)微的調(diào)整,從而開始熟悉該語言。
達(dá)克斯 (DAX)
What the heck is it? DAX, short for Data Analysis eXpressions, can be used to create measures and calculated columns once your data has been pulled into Power BI with Power Query/M.
這到底是什么? DAX是Data Analysis eXpressions(數(shù)據(jù)分析表達(dá)式)的縮寫,一旦將數(shù)據(jù)通過Power Query / M導(dǎo)入到Power BI中,即可用于創(chuàng)建度量和計(jì)算列。
When do I use it?: Using DAX is essentially like using formulas in Excel — it allows you to make calculations based on your data. You can use DAX to create calculated columns or measures.
我什么時(shí)候使用它?:使用DAX本質(zhì)上類似于在Excel中使用公式-它使您可以根據(jù)數(shù)據(jù)進(jìn)行計(jì)算。 您可以使用DAX創(chuàng)建計(jì)算列或度量。
計(jì)算列和度量 (Calculated Columns and Measures)
What the heck is it? Calculated columns and measures are two ways to write expressions in DAX. A calculated column will create an additional column with a value for every row in your table. A measure is an aggregate expression based on multiple rows in a table or multiple tables.
這到底是什么? 計(jì)算列和度量是在DAX中編寫表達(dá)式的兩種方法。 計(jì)算列將創(chuàng)建一個(gè)附加列,該附加列為表中的每一行提供一個(gè)值。 度量是基于一個(gè)表或多個(gè)表中的多行的聚合表達(dá)式。
When should I do a measure vs. a calculated column? I am so glad you asked because I really struggled with this one. This is a tricky one because you often can use either a measure or a calculated column (but that doesn’t mean you should).
什么時(shí)候應(yīng)該進(jìn)行度量與計(jì)算列比較? 您問我很高興,因?yàn)槲艺娴暮芘Α?這是一個(gè)棘手的問題,因?yàn)槟?jīng)常可以使用度量或計(jì)算列(但這并不意味著您應(yīng)該使用)。
Things to keep in mind with measures and calculated columns:
度量和計(jì)算列要記住的事情:
- When you can use either, use a measure - 當(dāng)你可以使用,使用措施 
- A calculated column is typically used when you want to use it as a filter or slicer or if you want to create categories (e.g. add a column that has “New Employee,” “Employee”, or “Senior Employee” based on their tenure). - 當(dāng)您想將其用作過濾器或切片器或要?jiǎng)?chuàng)建類別時(shí),通常使用計(jì)算列 (例如,根據(jù)其任期添加具有“新雇員”,“雇員”或“高級(jí)雇員”的列) 。 
- You have to use a measure if you want to calculate something based on what the user has selected (e.g. calculating the total revenue for a department selected in a slicer) - 如果要基于用戶選擇的內(nèi)容進(jìn)行計(jì)算,則必須使用度量 (例如,計(jì)算在切片器中選擇的部門的總收入) 
If the distinction between calculated columns and measures doesn’t totally make sense right now, that is okay. Try experimenting with creating both a measure and a calculated column and see if they both do what you need them to. If they do, great, use the measure. If just the calculated column works, go with that!
如果現(xiàn)在對(duì)計(jì)算列和度量之間的區(qū)別還不完全了解,那就可以了。 嘗試同時(shí)創(chuàng)建度量和計(jì)算列,以查看它們是否都滿足您的要求。 如果可以,那么請(qǐng)使用該措施。 如果只是計(jì)算出的列有效,那就繼續(xù)吧!
功率查詢(M)或DAX? (Power Query (M) or DAX?)
Things got confusing for me here, too, because it seemed like most of what I could do in Power Query/M, I could also do in DAX. Which one am I supposed to use and when?
這里的事情也讓我感到困惑,因?yàn)檫@似乎是我在Power Query / M中可以做的大多數(shù)事情,在DAX中也可以做到。 我應(yīng)該使用哪一個(gè)?何時(shí)使用?
Short answer: Use Power Query/M when possible. (The big exception here is if you are trying to create a calculated column that references a column in a different table. In that case, use DAX to create a calculated column)
簡短答案 :盡可能使用Power Query / M。 (這里的最大例外是,如果您嘗試創(chuàng)建引用不同表中的列的計(jì)算列,在這種情況下,請(qǐng)使用DAX創(chuàng)建計(jì)算列)
Long answer: Read this.
長答案:請(qǐng) 閱讀 。
Photo by Jingda Chen on Unsplash 陳靜 達(dá)在《 Unsplash》上的照片And just like that, you did it! You made it through a basic conceptualization of Power Query, M, DAX, measures, and calculated columns. If you don’t understand all the differences and uses right now, that is perfectly okay! You have laid the foundation, and that is a huge step in the right direction.
就這樣,您做到了! 您通過對(duì)Power Query,M,DAX,度量和計(jì)算列進(jìn)行了基本概念化。 如果您現(xiàn)在不了解所有差異和用途,那完全可以! 您已經(jīng)奠定了基礎(chǔ),這是朝正確方向邁出的巨大一步。
要記住的事情: (Things to remember:)
- Power Query is where it all begins to bring in and clean your data. Power Query是所有這些功能開始引入并清理您的數(shù)據(jù)的地方。
- M is the language used in Power Query (you don’t have to use M directly, but it makes your life easier in the long run). - M是Power Query中使用的語言(您不必直接使用M,但從長遠(yuǎn)來看,它會(huì)使您的生活更輕松)。 
- DAX is the language used once your data is in Power BI to create calculated columns and measures. DAX是將數(shù)據(jù)放入Power BI中后用于創(chuàng)建計(jì)算列和度量的語言。
- If you can do it in Power Query/M, you should (except when you are adding a column to a table that references a column in a different table). - 如果可以在Power Query / M中執(zhí)行此操作,則應(yīng)該這樣做 (將列添加到引用另一個(gè)表中的列的表中時(shí)除外)。 
- If a calculated column or a measure will work, use a measure. - 如果計(jì)算出的列或度量適用,請(qǐng)使用度量。 
下一步: (Next steps:)
The easiest, and fastest, way to get these concepts to stick in your brain are by exposure and practice. Here are some great resources to get started with:
使這些概念深入大腦的最簡單,最快的方法是通過接觸和實(shí)踐。 這里有一些很好的入門資源:
- SQLBI - SQLBI 
- Microsoft PBI Learning - Microsoft PBI學(xué)習(xí) 
- Power BI Community - Power BI社區(qū) 
While I highly encourage engaging with all the wonderful resources out there, nothing is going to help you more than by trying things out on your own and seeing what happens.
盡管我極力鼓勵(lì)與那里的所有寶貴資源合作,但沒有什么比通過自己嘗試和觀察會(huì)發(fā)生的事情對(duì)您有更多幫助。
Keep banging your head against the wall. It gets so much better.
繼續(xù)用力撞墻。 它變得好多了。
翻譯自: https://towardsdatascience.com/power-bi-m-vs-dax-vs-measures-4c77ae270790
總結(jié)
以上是生活随笔為你收集整理的Power BI:M与DAX以及度量与计算列的全部內(nèi)容,希望文章能夠幫你解決所遇到的問題。
 
                            
                        - 上一篇: 孕妇梦到蛇吞蛇预示着什么
- 下一篇: 梦到自己离婚什么预兆
