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 !
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?
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.
Maybe try Mike Girvin’s playlist here. ru-vid.com/group/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/
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
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 😀
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
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?
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/
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?
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.
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 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.
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?
You would write a Measure like: Actual =CALCULATE( SUM( YourData[ YourValue column] ), YourData[Scenario]=“Actual” ) Repeat for budget Then Variance = [Actual] - [Budget]
@@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.
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
@@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?
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.
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?
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?
