DAX and the Data Model

If you want a basic explanation of DAX and Data Models in Excel and Power BI then you've come to the right place.
This brief introduction to DAX and Data Models aims to give you a general starting point understanding of what these things are and where / why they are used.
If you've heard people talking about "Modern Excel" this is what they are referring to
Loading 10 Million Records into the Excel Data Model • 10 Million Rows of dat...
What is DAX : • What is DAX?
What is Power Query: • What is Power Query?
Link to Power Query Calendar Files (Excel and Power BI) and other templates
accessanalytic.com.au/free-ex...
00:00 Intro
00:25 Background
02:50 Use Power Query to load data to Data Model
04:13 Launching Power Pivot (Manage Data Model)
04:40 The Diagram (Model) View
07:18 DAX
12:00 Year To Date calculation
15:45 Prior Year calculation
Follow me on LinkedIn
/ wynhopkins
Twitter
/ wynhopkins
Access Analytic Training
accessanalytic.com.au/training

[@DanielTavares29]

Lets start from zero...good video!!

[@AccessAnalytic]

Cheers Daniel

[@azwarmzafar]

Perfect one, thanks.

[@AccessAnalytic]

Cheers

[@IvanCortinas_ES]

Excellent video. As always, a great explanation. Thank you Wyn!

[@AccessAnalytic]

Thank you for watching and commenting Iván

[@mrook9053]

So helpful for people just starting out! Love your content

[@AccessAnalytic]

Thank you for leaving a comment and letting me know you found it useful

[@mikheilitchulukhadze8554]

Your explanation is very great, please add more power BI videos

[@AccessAnalytic]

Thanks… here’s my Power BI playlist Power BI kzbin.info/aero/PLlHDyf8d156VDobBIk13o4mZLk19DbV81

[@lorenzoladejobi8701]

Very insightful ! Thank you Sir.

[@AccessAnalytic]

You're welcome

[@wayneedmondson1065]

Hi Wyn. Well explained and useful tips and information! Thanks for sharing :)) Looking forward to more on DAX and the Data Model. Thumbs up!!

[@AccessAnalytic]

Appreciated as always Wayne

[@sajilpulath7353]

This was way cool easy to understand the basics of creating relationships and measures...loved it 👍 it would be great if you could do video for explaining more other commonly used measures !

[@AccessAnalytic]

Thanks Sajil, I’ll be doing a DAX video in coming weeks

[@louism.4980]

Thank you for introducing this! :)

[@AccessAnalytic]

You’re welcome

[@Redant1Redant]

Great presentation. Very clear.

[@AccessAnalytic]

You’re welcome

[@zuhairalmutawa9941]

Great and professional presentation. Thank you ...

[@AccessAnalytic]

You’re welcome Zuhair, thanks

[@zaheerahmad3896]

GOD bless you !

[@pgplanas]

Who else was blown away by Excel's data model? And here I was thinking that I was an advanced Excel user because I knew how to use INDIRECT.

[@AccessAnalytic]

It's amazing what Excel is capable of these days Pablo

[@FRANKWHITE1996]

Thanks

[@AccessAnalytic]

You’re welcome

[@utubeAgape]

WYN, this was great! So helpful to see you write the DAX measures. I am wondering how you learned all the DAX functions? I have been building PBI Dashboards using implicit measures with the easy drag and drop method but now getting a little stuck on the advice to use explicit DAX measures instead. How do I learn the DAX functions that are available and which measures to build for my dashboards? Same goes for the Calendar Table - I have not been building them so far but it sounds like I should - is this true?

[@AccessAnalytic]

Hi Irene, DAX measures such as SUM and COUNTROWS, plus a Calendar table are important foundations of a flexible report. You can often get by without them but I’d recommend you get into good habits early and use them. I’d recommend this book for learning DAX exceleratorbi.com.au/supercharge-power-bi-book/

[@utubeAgape]

@@AccessAnalytic Thank you for the prompt response - I appreciate the advice and I will check into that book👍

[@johnywhy4679]

10:22 "please don't use implicit measures don't use the drag and drop type stuff it just automatically happens be specific write an explicit measure" - If the implicit measures using drag and drop return the same output, then what's the problem?

[@AccessAnalytic]

Hi Johny, it's a more robust future proof method of building a report. As the report or data sources change over time or you add an extra measure here and there you can easily tweak a base measure and have it flow through to visuals and other measures. However if you drag and drop columns you have to drag and drop new fields in / rebuild visuals etc which is time consuming and you could potentially miss something. It takes slightly longer to do but it pays off in the long run.

[@johnywhy4679]

@@AccessAnalytic Make sense! But isn't drag/drop a helpful way to autogenerate the basic code, and then tweak it by hand?

[@excelworx8712]

Well explained, Wyn. Can you refer us where we could learn about more DAX formulas

[@AccessAnalytic]

Maybe try Mike Girvin’s playlist here. kzbin.info/aero/PLrRPvpgDmw0nglJ9yX2XT5-K1A_AKHpvW For a book see exceleratorbi.com.au/supercharge-power-bi-book/ For online training see exceleratorbi.com.au/supercharge-power-bi-book/ For more advanced DAX www.sqlbi.com/training/

[@excelworx8712]

@@AccessAnalytic thanks again for the comprehensive reply, Wyn!

[@Dan_De_Man]

In your date table tblcalendar how do you get your months i.e Jan Feb. I used text formula but on my report pivot months don't appear but they appear if i drag in month number I tried text([@date], "mmm") but that didn't work either

[@AccessAnalytic]

@@Dan_De_Man - check out this video regarding the use of a Calendar table: kzbin.info/www/bejne/gpeunmZ3ic6Vpqc

[@mandypaulissen]

DAX is the trickiest part of Power XX.... once its mystery is cracked, all the rest of pieces can fall in places much easily.

[@AccessAnalytic]

Yep it’s a challenging beast

[@DPaquette]

Wyn when you look at the diagram view and there is no connection but the tables specify that there is a connection at the bottom of the page... how do you find out where this connection is?

[@AccessAnalytic]

I'm not sure what you mean when you say no connection and that there is a connection at the bottom.

[@DiptaGhossan]

Hi Wyn, do you have any videos that solve a challenge to calculate items in a column? just like the way it does in normal pivot.

[@AccessAnalytic]

Can you give an example of what you mean please

[@DiptaGhossan]

@@AccessAnalytic in normal pivot, there is a function to calculate field items. i am looking for a way to do this in power point

[@DiptaGhossan]

calculaten items i mean, calculate filed is calculating columns, whils the first one is more rows within 1 col. i.e. i have 1 col "Scenario" that consists of Budget, Forecast, Actual in 1 col. how to calculate variance of budget to actual without pivoting this into column?

[@AccessAnalytic]

You would write a Measure like: Actual =CALCULATE( SUM( YourData[ YourValue column] ), YourData[Scenario]=“Actual” ) Repeat for budget Then Variance = [Actual] - [Budget]

[@philipos22]

Thank you for the video. When i open my data model, it shows only a blank screen, even though i have 2 tables loaded with power query in the file. do you have any idea how to solve this ?

[@AccessAnalytic]

Hi, Power BI or Excel? Sometimes the tables appear off to the far right side, hidden behind the side panels. Zoom out.

[@philipos22]

@@AccessAnalytic thank you for your answer, it is in excel. meanwhile i found the solution, i had to load the queries data to the data model by right clicking on the query in the excel window on vertical right ribbon.

[@AccessAnalytic]

Ah yeah, that does catch out folk at the start sometimes

[@zembasiamuleya9013]

This is good. However, when I switch (close and open a different Excel sheet) after using DAX I am getting the below error message: We couldn't get data from the data model. here's the error message we got: The ' '... measure cannot be created because a column with the same name already exists. Please help, how do I resolve this issue? Thanks.

[@AccessAnalytic]

Difficult to say without looking at the file, I’d open the data model and rename any measures that clash with existing column names

[@paulvanobberghen]

Can’t you just load an Excel table into the data model via the Power Pivot tab and click on, well, « Add to the data model », that is, without going through Power Query?

[@AccessAnalytic]

You can ( it will work ok ) but it’s not considered best practice and locks you into that approach. It was added before power query became an option. Since Power Query came along it provides flexibility as requirements change in the future.

[@murjika]

Hi, I have a table with invoice line items. Each line repeats the invoice number as many times as the line item on it, plus each line has a column to indicate customer ID. I want to summarise per customer ID total value sold, which is easy as I just do pivot, but then next to each customer in that pivot I want to show how many invoices were made to that customer. It does not work if I simply count the lines because then it just shows total number of line items and not total number of invoices. I somehow need to count unique invoice numbers but I don't know how. I could add invoice nr as a second index column but the problem is that my data is too large and Excel crashes (total lines in the pivot go well above 1 million). Any advice?

[@AccessAnalytic]

If you load the data to the data model then you can use a pivot table and do a DISTINCTCOUNT.

[@murjika]

@@AccessAnalytic Thank you so much!

[@johnywhy4679]

Is it correct to say the data model feeds ONLY pivot tables, not flat tables?

[@AccessAnalytic]

Yep, correct. Power Query can load to a flat table but Power Pivot is Pivot Tables

[@johnywhy4679]

@@AccessAnalytic Thx!

[@johnywhy4679]

@@AccessAnalytic Thx for that! Is the following correct? DAX is the language of PP, and is best for obtaining aggregate values. M is the language of PQ, and is best for obtaining atomic, scalar values.

[@AccessAnalytic]

DAX is for creating dynamic calculations, averages, cumulative totals, prior period comparisons, variances, ratios etc. Power Query M language is for data clean up and re-shaping plus adding calculated columns if required

[@johnywhy4679]

@@AccessAnalytic Thanks! Sounds like there's some overlap, correct? But DAX is more for aggregate functions, like cumulative totals and averages, right? M is more for flat tables, right?