DAX and the Data Model

[@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 😀

[@AccessAnalytic]

Power BI was indeed the child of Excel. In 2009 the Power Pivot interface and data model was added to Excel as an addin, with Power Query addin previews arriving in 2010. Both were then built into Excel 2016. Power Bi desktop took those features and hence there’s a big commonality of functionality

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

[@FramaKIRK]

I think I get the understanding of how this data model links have to be organized to work. The query with the connections to the calendar and the other query has to be placed in the values section of the pivot table given it's connections to both TIME/ DATE and To THE OTHER TABLE you want to slice with.

[@AccessAnalytic]

Glad you worked it out. Best practice is to put measures in the values field. I.e. write a measure that refers to your Fact table ( good for future proofing )

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

[@Redant1Redant]

Great presentation. Very clear.

[@AccessAnalytic]

You’re welcome

[@DanielTavares29]

Lets start from zero...good video!!

[@AccessAnalytic]

Cheers Daniel

[@IvanCortinas_ES]

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

[@AccessAnalytic]

Thank you for watching and commenting Iván

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

[@zuhairalmutawa9941]

Great and professional presentation. Thank you ...

[@AccessAnalytic]

You’re welcome Zuhair, thanks

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

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

[@lorenzoladejobi8701]

Very insightful ! Thank you Sir.

[@AccessAnalytic]

You're welcome

[@louism.4980]

Thank you for introducing this! :)

[@AccessAnalytic]

You’re welcome

[@FramaKIRK]

Hey Friend, I appreciate your video on this topic. I've got an excellent data model created because of your instructions. I am having a problem with getting the data model to work as you have demonstrated in this video by adding data from a different table to my pivot table given the relationship in the data model. I have connected them by the same column as you did and for the calendar table as well, but I cannot pull in the column from a different table into my pivot to slice and dice my data. Do you know what setting or step I might be missing, Sir? Thank you for your help, Kirk Junior

[@AccessAnalytic]

I note you’ve said you’ve sorted this out in another comment.

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

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

[@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]

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

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

[@azwarmzafar]

Perfect one, thanks.

[@AccessAnalytic]

Cheers

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

[@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👍

[@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!

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

[@zaheerahmad3896]

GOD bless you !

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

[@FRANKWHITE1996]

Thanks

[@AccessAnalytic]

You’re welcome