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

Link to Power Query Calendar Files (Excel and Power BI) and other templates

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

Twitter

Access Analytic Training

[sledgehammer-productions]

I remember doing a Power BI Course in 2018 I thought "this is the lucky child of Excel and Access". Then in 2020 I found that the Power Query part of BI was now an integral part of Excel. And now (2024) I learned that my aversion to pivot tables kept me away from Power Pivot that again, seems to integrate so much more again. And it feels Power BI should feel more 'natural' now to an Excel enthousiast. Can't wait to actually build stuff with it, preferably paid for by my employer 😀

[IvanCortinas_ES]

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

[sajilpulath]

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 !

[mrook]

So helpful for people just starting out! Love your content

[wayneedmondson]

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

[zuhairalmutawa]

Great and professional presentation. Thank you ...

[mikheilitchulukhadze]

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

[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.

[johnywhy]

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?

[excelworx]

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

[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.

[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?

[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?

[philipos]

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 ?

[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.

[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?

[zembasiamuleya]

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.

[johnywhy]

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

[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?