Skip to content
TheChatArchiveThe Chat Archive
Metallama-4-maverick-03-26-experimental· MetaHugging Face LMArena Human Preference 140K

Streść mi w postaci pytań i odpowiedzi: "In this video, I'd like to give you the update…

From a Chatbot Arena battleThe same person sent this prompt to two anonymous models at once. Compare it with Mistralmistral-small-2506’s answer.
  1. User

    Streść mi w postaci pytań i odpowiedzi: "In this video, I'd like to give you the update which Microsoft released in the new July 2023 version regarding the on object feature. Now, the on object feature means that we can select any kind of visual like this one, for instance. And then based on our selection, we can directly click on these icons here to either choose the data we want here as well as, as you can see here, what kind of visual we want. Currently it's this donut chart, but I can switch it here if I want and we can also go up here and do our formatting. Now if you work with Power BI in 2022, for instance. So in earlier versions, then you were actually more familiar with these options here actually pinned up there on the upper right. Now if you want to switch this back, you can easily do this using the pane switcher, which means that you can click on this little icon here and you can say move to pane switcher. And if you do that, then you see that. Now you have the box here with all the visuals up there on the right side again, and then you can use it here. So it depends on what you prefer and you can easily switch it back by simply click this icon again here and then say you want to move to visual and then it's back on the visual itself. So it's really up to you what kind of option you want to use. This is now available for you. Besides doing it here, you could also go under the View tab and there's also this option pane switcher, and you can go there and then you can say, I want to move here to the paint switcher and then you can switch it here or you can of course go back there again and you can act untick this option and then it's back on the visual. If you're just wondering what this actually means, this always show pane switcher. This just means if you take a look at the little bar here at the side, the data view, if I untick this here, you see that this is gone. That is the only thing which this does. And the second thing which is new is that beside clicking on these icons here, to add them here to the pane, like for instance, if I click on format, you would see here there's also the format option here. If I click on bookmarks, I would find the bookmarks and so on. So this is an option. And new in the July 2023 release, you can also click on the plus symbol here. So customize pane switcher. If you click here, then you can simply check here the boxes, what you want to see and what not. So if I go to the selection, you will see that this also appears in here. So it's up to you how you want to do it. But this plus symbol now is also available for you. I just wanted to mention this because in the videos when I recorded that, that was not available. And just to have you have the full experience here and know where you can find everything, I want to give this video here to you. So hopefully that was helpful.

    Now to get started, we need to set up our data model and you find the data attached in the resource section to follow along. And this is what I highly encourage you to do, because this is a hands on course and that's the way how you learn power. BI So let's get into that. You already see I opened a complete new Power BI file, so if you have not done so far, please do the same. So make sure to be able to follow along. So to get some data we have now the option to go under the home ribbon here and then choose one of the data options up here. Or we could also use one of those which are available in here. Or if the one source which we are trying to leverage is not there, you could go to get data from another source to open the navigation window. For us now it's quite easy because we have an Excel file, so I can go to import data from Excel, click this option, and then the window or in this case appears where it allows us to actually put the get the data which we're going to use. And in this case is this Pi underscore training XLS X, that's the file. So I like to select it. You can either double click it or click on it and then click on Open. So then the navigator window appears and here we have actually the well, the content of the Excel file. And you can clearly see there are different icons. So if you have already taken my first power bi course, then you probably know that the difference here is that the upper ones are tables from the Excel file. The lower ones are the complete excel sheets. And as a best practice tip, I would always recommend you to go with the tables just in case they are available, because then you make sure you only import the relevant data from this table and not additional data which might also be on the specific sheet. So that's why I will click on the employee table. And this also gives us a little preview of what the data looks like. And you see that we got employee names and the surnames, also the country. And I can already spot here that we have a little issue because currently here there are missing values. So these null values simply refers to missing values, which we need to fix when we open it in the query. Editor. Okay, so what else do we have? We have the orders table. Let's select this one and also check it. And in here we have various columns which look fine at first glance. But of course we need to check this in detail when we go in the advanced editor. So in the power query editor. So otherwise we have also the suppliers. Let's check this option here and we can see that this is the suppliers list. Okay, that looks fine. Okay, so let's select those three for now. And then we need to transform the data because the data, as we've seen, is not yet in the right format. And even if you think it is in the right format, I would always recommend to transform the data first and check it in the query editor because you can never be completely sure that there is not something you want to actually transform first. And even though you have the option to go back to the query editor later on, it's always best practice to always have a look at the data first and only if the data is really the way you want it, only then load it into power. BI And this of course is especially true if you have a really large dataset, then always, always, always go to the query editor first and then check out whether you really need everything you can see there. So let's actually go to transform data, click on it, and then we wait. And here is our navigator window and we can actually go to the employees first because there we already spot that there is an issue which is the office team in here. So this is something we need to fix and we can easily do this by using the options which the Power query editor provides to us. In this case, I want to fill those missing values. That is why I go to transform. And under transform we have various transformation steps and one of those is fill. Now there are options to fill up and fill down, so let's just check. What do we need actually? Well, the first one, the first value is inserted here. The other ones are missing. So we clearly want to fill down. Right. So for us, it's Team Targaryen. That is why we need to fill those values down and here for UK Team Lancaster. So we need those values down. So let's do that. Select the column first. You can clearly see in the black bar at the bottom at the top, sorry, that there are currently missing values. But if we fix it by simply fill down, click on fill down. You see that now they are filled and no data is missing anymore. Also keep in mind that you always have, if you right click on a column, also various transformation steps which are there. And there's also, for instance, the fill option. So you could also do it from here. This is what I personally call redundancy in power BI but not in a negative way, but more in a way that it allows us to do the same transformation on various places in power. BI Which I personally think is helpful because sometimes I do not find the default setting maybe immediately and then I can use the other one. So it's easier to find, at least for me. So okay, now we filled this and we also want to check before we go what is actually here, the data type. I can see this is one, two, three. ABC. ABC. ABC. That looks fine. Especially because the employee ID is the ID. We want to link to our model later on. That is why I would like to have here a number and make sure that the employee ID inside our facts or orders table is also a whole number. The last thing I'd like to do is actually rename this and some one of my preferences is always use something like TBL at the end to to identify this as a table. So I press TBL, press enter and here I got my employees table. Now for the orders. Let's check the orders and let's just see. Okay. It looks like here that currently the data has not been transformed correctly for whatever reason, because you can see here this ABC one, two, three. This is ABC one, two, three. So obviously the data type has not been assigned correctly. I don't know why this happens. This actually most of the time it works properly, but that's actually fix this. Let's just see what we can do. So there is one option. So let me actually do the following. Let's select everything we have. So Ctrl A, and then there's an option to detect data type. So let's actually click this and now we can see that the change type step has been applied. And also here are data types. Now of course we still need to check whether these are correct, but this already helps instead of doing this manually step by step. So let's just check. Order ID is a whole number. That is fine. And this is text. That's an ID employee ID important. This is also a whole number which is the same as employees table. That is fine. Then we have ordered date. It's a date. Ship date is currently an issue. Ship date obviously is not considered as a date currently. So this is something we need to fix. So let's try that right click left, click here and right click also would work, by the way, because there's also change type option here. But let's left click here and then let's just see if we transform it into a date. Please note it's not a date time because there is no time attached to that. But if we try to transform it to a date, click this option. So now it asks me, do I want to change here the current setup or replace the current step or add a new step? I always like to go with the new step because this makes sure that even though or even if my replacement runs into an error, I can easily remove it and do not remove or have to remove the whole change type from this step here. So let me click on Add New Step and you see that exactly what I was assuming it ran into an error. So if you have taken the first master class course beginner to advanced, then you now know how to fix it. But if not, no worries. The problem here is that it is not. It's not able to transform the number directly into a date. So we need to have some kind of bridge, if you want to call it this way. So some kind of intermediate step. So for that, let me just remove this change type and then let's first convert this into text because converting something into text regarding the data type actually always works. So let's click on this text. And again, I will add a new step like that. Okay. And this does not throw any errors. So the bar here is still green. And I can see here now the values left aligned, which tells me this is text. Next, I want to convert this from text into a date, so converting it from number to text is the step or intermediate step we do, and then we convert it. We convert it into a date from the data type text. So let's do that. Let's actually click on date. And again, I don't want to replace the current step. I want to add a new step. So click on New Step. And now you see that this worked exactly as we want it to work. So now we have also the little calendar icon and we have here the date type. So far, so good. So let's scroll further to the right. What else do we have? Ship mode. This is fine. The data type seems to be correct. Selling price, amount, decimal numbers, whole numbers. That all looks fine to me. So let's scroll further to the right and let's just see. Okay, so what I can or what I realized the supplier ID that's important because that's the column we want to link to the supplier table. And this is a whole number that is already a good sign. And what else do we have? The region is also fine. Customer zip code. There is a few data missing. You can see here there are 29 entries which are missing. However, in this case it is not that important to us because we will not use this column. That is why, regarding best practices, you could actually right click here and say, I want to remove this column because if you do not use a column at all in the data model, there is no need to keep it. And this of course is best practice because then you shrink the size of the whole data model. In this specific case. So in this use case we have here, it does not really matter because our model is really small. And if you have a tiny model, it does not really matter whether you delete columns you don't need or whether you want to keep them. So we keep it inside now. But just to mention this, if you have a big model and work with large enterprise models later on, always make sure you remove everything you don't need. Okay? So like that, we just keep this. That is fine. And also, we agree that the missing values do not really bother us in this specific use case. However, as I said, if you have this in the company environment and it is important where the customer lives because you need to send something to his or her address, then of course make sure that you go to the people who are in charge here, for instance, your sales department or anything like that and ask to fill this missing values. So what else do we have? Well, let me just check If I go to my supplier table, I see that the data type is one, two, three. So that is fine. But I can also see that the country here is USA and because country here is USA, I like to actually also have in my orders table the country name as USA. Because here it is actually specified, as in this case, if I go to my countries here, let's just check that. And there actually is a us, right? So either I need to keep it us or I need to transform it into USA. Now, having said that, US is probably more common. And actually if I go to the employees table, there's also specified us. So maybe it's even better to fix the USA in the suppliers table and transform it to us. So are we going to do this? So let's click on the country here and say, we would like to replace values. And this can be done either by transform under transform the replace values option or you right click in the country. And there's also replace values option. So if you go in here, replace values, then you can actually go in here and just say the values defined in this case USA. So type in USA and you want to replace it with, in this case, us. And then let's do that. Let's click okay. And now you see that all the US names in this case the suppliers from the US or USA in this case have been replaced with us. So that's the way how you can replace values. The other way, which you might be familiar from Excel, does not work. So you cannot simply click in one cell, double click here and edit this. This doesn't work in power BI, but the replace values gives you some options to transform this. And of course you could also write additional transformations if you go to Add column and you can create, for instance here custom code. If you want to sort something that's also possible or use the great column from Examples feature, which you also checked out in a different course. So that's also available in here. Okay, so we got this as well. And the funny thing here is actually this is the supplier, CBL, so I call it supplier supplier TBL Enter. And I should also actually use this and say employee TBL so M employee, employee TBL call it this way. And then finally the orders table. Go back to the orders. I would like name to name this orders TBL so orders TBL press enter. And now I've also renamed the orders so quickly check the orders one more time. If I scroll to the right, there's something else I wanted to change. Let me just see that. What I can see the regions, the customer country now has been fixed and the only thing I think, which I also spotted is inside the customer country. There is actually currently a wrong entry, which is zero. So let's also fix this. And the way we want to fix it in our case is that the country zero. Of course, in a real world scenario, you need to ask what this is. But in this case, the zero belongs to us. That is why I can go right click in the customer country one more time, replace values. So here it is, and then just search for the zero entry and replace it here with us. And now let's click okay and we are good to go. And if you take a look at the filtering option, there is no zero anymore. If you see a hint like that, always click on load more to see all the values, but you can still see there is no zero. Okay, so we got this fixed as well. So let's click okay. And now we actually have our three tables. The final table we need. If you're familiar with that, is, as I always recommend, a proper date table, because whenever you want to do any kind of time intelligence in power BI. So using the date add function, same period last year function and all the other kinds of time intelligence functions which are available in power. BI. Then you want to make sure that you do them based on a proper date table and a proper date table itself is actually quite easy because all it is, is a table which covers the whole time frame you're going to analyze in your model, but without any missing values. So a proper dates table would actually already be enough. If you have just one column with dates and each day is unique and it simply covers, in this case, three years, which we have in this dataset. So starting in 2021 until the end of 2023, that would be enough. But if you want to maybe add, give more and more, add more information to the dates table, this is where this helper table comes into play and I have it in a resource section of this course. You can download it, which is here, let me just take this the wrong which is here, and that's called date table code. So let me just open this. And this is the code actually. So let me just maybe add it in a different window. Let me show it to you. So if you don't know this already, go to the editor here. Open it. And that's what the code looks like. So it's quite a lot to digest. I get that. But actually all it is, is simply a m code, which is used in the power query editor, and it creates a few additional columns for our date table, like what kind of weekday it is, what's the name of the month, and so on. And it is a function. So it allows us to control Z to copy it and then control V to paste it in here by simply going to home and then say new source, add a blank query. And then we just need to go to the advanced editor here and paste our query. So remove the template which is inside. Paste the query from the code and you should see no syntax errors. As soon as you can see that, click on Done. And now you have here the function for the date table. So for the starting date here, we can start with the 1st of January in 2021 because my current date range here in the orders table is from 2021 to the end of 2023. So the end date will be the 31st in this case of December in 2023. The financial year start month is one because we do not have a different financial year holidays table. We don't have one, so there's nothing to choose from. If you have that, you could do it. But of course you need to provide this table. So then we need to invoke the query, click on Invoke and you see that now we have our dates table. So including the most important column, which is this first one which contains our unique list of dates for this date range, and then of course, all the additional columns. So let's rename this properly and call it dates TBL. So to be consistent here, of course you can also drag this up if you want. And now we could create right click here and create in this case, groups. So different kinds of folders to add our tables. But in this case there is not really much to process for staging and transformation and so on. So that's why I will just keep it like that. So this is it actually, for creating the tables. And as soon as we're done with that, we could now click on close and apply and load the data in Power BI desktop. So click on Close and apply and then we just wait until the loading happens. And then we should see the tables just in a few seconds and there they are. So here are our tables. And now we can of course start with our reports. But one very, very, very important thing we need to do first is creating the relationships for our data model. So setting up the data model correctly. To do this, we need to go to the model view here, click on Model View. And then let's just let me just zoom out a little bit. We can see that we got our effects table so that the table we want to analyze in here, which is the orders and we got our dimension tables here for filtering, which is suppliers, table and employee table. And those two have already been mapped correctly. Have they? Well, let's check that. If we hover over the relationship, we can see employee ID to employee ID this relationship. And it's also a one to star relationship, So one to many. And it also has a filter direction from the one side to the many side. So exactly what the perfect structure looks like. And the same is true for the pliers. Hover over this and let's just check. I need to maybe scroll down a little bit. Where is the supplier ID? So supplier D at the end here. So let's just check, hover over it and you see that the two columns suppliers are highlighted. So hopefully you can see that if I hover over that like that, you see that these are highlighted. So obviously the relationship also correct. And please note I'd like to spot this out. Supplier ID here is written with a dash here. Supplier D is written together. So this is something is still was able to to figure out the data table connection is currently missing. But this is actually expected because here it's called date. And if I take a look at my orders table, I have a ship date here and I also have another date which I want to use. Here is the orders date and the order date. Of course, the name is completely different from date, so the Power BI was not able to figure out that this belongs together. But that's not an issue because we can simply place it somewhere and then we can simply drag and drop drag date on order date to create relationship. And we see that it's also a one to many, so one to star relationship. So this is actually also fine now. Of course, you can drag the tables around and place them differently. I personally like to have this flow that the dimension tables, so the tables I'm going to use to slice and dice my data are on top and the fact table is at the bottom. But this is just a personal preference. You can do that a different way if you want. So that's actually it for setting up the data model. And now we could go into the report view and get started with Power BI, with Dax, with visualization and all the cool things we can do in Power BI. So that's it for this video. Hopefully we were able to follow along. If not, then please now open Power BI and do the model setup yourself."

  2. Meta Assistant

    Oto streszczenie treści w postaci pytań i odpowiedzi:

    Pytanie 1: Co nowego pojawiło się w funkcji "On Object" w Power BI w aktualizacji z lipca 2023? Odpowiedź: W aktualizacji z lipca 2023 funkcja "On Object" pozwala teraz na bezpośrednią interakcję z obiektami wizualnymi (np. wykresami) poprzez kliknięcie na nie i wybór opcji formatowania lub danych bezpośrednio z poziomu obiektu, zamiast konieczności korzystania z górnego paska narzędzi.

    Pytanie 2: Jak wrócić do starego sposobu pracy z opcjami wizualizacji z Power BI 2022? Odpowiedź: Aby wrócić do starego sposobu (z opcjami po prawej stronie ekranu), kliknij na małą ikonę przełącznika (Pane Switcher) na obiekcie wizualnym i wybierz "Move to Pane Switcher". Można to również zrobić przez zakładkę "View" i opcję "Pane Switcher".

    Pytanie 3: Co oznacza opcja "Always show Pane Switcher"? Odpowiedź: Ta opcja decyduje o widoczności paska przełączania widoku (np. między danymi, modelem, raportem) po lewej stronie. Jeśli odznaczysz tę opcję, pasek ten zostanie ukryty.

    Pytanie 4: Jak dostosować widoczne ikony na panelu obiektu wizualnego? Odpowiedź: Od lipca 2023 możesz kliknąć ikonę "+" (Customize Pane Switcher) na obiekcie wizualnym i wybrać, które ikony (np. formatowanie, zakładki) chcesz widzieć na panelu.

    Pytanie 5: Jak zacząć pracę z danymi w Power BI w tym kursie? Odpowiedź: Należy pobrać plik Excela z danymi z sekcji zasobów kursu, otworzyć Power BI, wybrać opcję "Get Data" > "Excel", wskazać plik Pi_training.xlsx, a następnie wybrać trzy tabele: Employee, Orders i Suppliers.

    Pytanie 6: Dlaczego lepiej importować dane z tabel Excela zamiast całych arkuszy? Odpowiedź: Tabele Excela zawierają tylko istotne dane, podczas gdy arkusze mogą mieć dodatkowe, niepotrzebne informacje. Importując dane bezpośrednio z tabel, ograniczasz zbędne dane w modelu.

    Pytanie 7: Jak rozwiązać problem brakujących wartości (null) w kolumnie "Office Team" w tabeli Employees? Odpowiedź: W Edytorze Query (Transform Data) wybierz kolumnę "Office Team", przejdź do zakładki "Transform" i kliknij "Fill" > "Fill Down", aby uzupełnić brakujące wartości danymi z góry.

    Pytanie 8: Jak zmienić typ danych kolumny, jeśli automatyczne wykrycie nie zadziałało (np. dla daty)? Odpowiedź:

    1. Zmień najpierw błędny typ (np. "ABC") na tekst ("Text"), dodając nowy krok.
    2. Następnie zmień tekst na datę ("Date"), również dodając nowy krok, aby uniknąć błędów.

    Pytanie 9: Dlaczego warto usunąć nieużywane kolumny w Query Editor? Odpowiedź: Usuwanie nieużywanych kolumn zmniejsza rozmiar modelu danych, co jest szczególnie ważne przy dużych zbiorach danych w środowiskach enterprise.

    Pytanie 10: Jak zastąpić błędne wartości w danych (np. "USA" na "US" lub "0" na "US")? Odpowiedź: W Query Editor kliknij prawym przyciskiem na kolumnę > "Replace Values", wpisz wartość do zastąpienia (np. "USA" lub "0") i nową wartość (np. "US").

    Pytanie 11: Po co tworzyć tabelę dat (Dates TBL) i jak ją przygotować? Odpowiedź: Tabela dat jest niezbędna do analiz czasowych (Time Intelligence). Użyj kodu M (dostępny w zasobach kursu) w Edytorze Query:

    1. Utwórz "Blank Query",
    2. Otwórz "Advanced Editor",
    3. Wklej kod i ustaw daty od 01.01.2021 do 31.12.2023,
    4. Kliknij "Invoke", aby wygenerować tabelę.

    Pytanie 12: Jak utworzyć relacje między tabelami w modelu danych? Odpowiedź:

    1. Przejdź do widoku modelu (Model View),
    2. Power BI automatycznie wykryło relacje między Employees i Orders (po Employee ID) oraz Suppliers i Orders (po Supplier ID),
    3. Ręcznie utwórz brakującą relację: przeciągnij kolumnę Date z Dates TBL do Order Date w Orders TBL.

    Pytanie 13: Jak ustawić właściwy układ tabel w modelu danych? Odpowiedź: Umieść tabele wymiarów (dimension tables: Employees, Suppliers, Dates) na górze, a tabelę faktów (fact table: Orders) na dole. To tylko kwestia preferencji, ale poprawia czytelność modelu.

    Pytanie 14: Co zrobić po przygotowaniu modelu danych? Odpowiedź: Kliknij "Close & Apply", aby załadować dane do Power BI, a następnie przejdź do Report View, aby zacząć tworzyć wizualizacje i analizy z użyciem DAX.

    Gotowe! To wszystkie kluczowe kroki opisane w filmie. Teraz możesz samodzielnie odtworzyć cały proces w Power BI.

  3. User

    Napisz więcej na temat: "Transform" i kliknij "Fill" > "Fill Down", aby uzupełnić brakujące wartości danymi z góry.

  4. Meta Assistant

    Oto szczegółowe wyjaśnienie dotyczące "Transform" > "Fill" > "Fill Down" w Power Query Editor w Power BI. To jedna z najczęściej używanych operacji podczas czyszczenia i przekształcania danych.

    Czym są brakujące wartości (null, missing values)?

    Podczas importowania danych często napotykamy brakujące wartości, oznaczone jako:

    • null (w Power BI/Power Query)
    • NA (w Excelu lub CSV)
    • Puste komórki (w Excelu)

    Brakujące dane mogą wynikać z:

    1. Niewprowadzenia informacji przez użytkownika (np. zapomniano dodać kraj lub zespół).
    2. Błędów podczas eksportu danych (np. niedziałający formularz internetowy).
    3. Połączenia danych z różnych źródeł (jedno źródło miało daną kolumnę, inne nie).

    Dlaczego trzeba uzupełnić brakujące wartości?

    1. Analizy nie zadziałają poprawnie: Jeśli w kluczowych kolumnach (np. Office Team, Region) są luki, filtrowanie, grupowanie czy obliczenia mogą być błędne.
    2. Łączenie tabel (relacje) będzie niemożliwe: Jeśli klucz łączenia (np. Employee ID) ma null, rekordy nie zostaną dopasowane.
    3. Wizualizacje będą niekompletne: Wykresy pomiją rekordy z brakującymi danymi, co zafałszuje wyniki.

    Jak działa "Fill Down" (Wypełnij w dół)?

    Wyobraź sobie dane w takiej formie:

    Employee IDNameOffice Team
    1AnnaTeam A
    2Bennull
    3Clairenull
    4DavidTeam B
    5Evanull

    Chcemy, aby null przy Benie i Claire zostało zastąpione przez Team A (bo to najbliższa wypełniona wartość z góry), a null przy Evie przez Team B.

    Krok po kroku: Jak użyć "Fill Down"?

    1. Otwórz Power Query Editor:

      • Po zaimportowaniu danych kliknij "Transform Data" (lub "Edytuj zapytanie").
    2. Znajdź kolumnę z brakującymi wartościami:

      • W naszym przykładzie to Office Team.
      • Kliknij na nagłówek kolumny "Office Team", aby ją zaznaczyć.
    3. Sprawdź, czy są luki:

      • Na dole ekranu (w status bar) Power BI wyświetla informację, np.: "123 rows, 30 errors or empty values" – to sygnał, że mamy null.
    4. Przejdź do zakładki "Transform":

      • Na górnym pasku znajdź kartę "Transform" (nie "Home" ani "Add Column").
      • Kliknij "Fill" (jest w środkowej części paska).
    5. Wybierz "Fill Down":

      • Z rozwijanego menu wybierz "Fill Down".
    6. Zobacz efekt:

      • Power Query przejrzy całą kolumnę od góry do dołu i:
        • Jeśli znajdzie null, weźmie ostatnią znaną wartość (z poprzedniego wiersza).
        • Wstawi ją w miejsce null.

    Nasz przykład po "Fill Down":

    Employee IDNameOffice Team
    1AnnaTeam A
    2BenTeam A
    3ClaireTeam A
    4DavidTeam B
    5EvaTeam B

    Jak to wygląda w kodzie M (zaawansowany widok)?

    Jeśli klikniesz "Advanced Editor", zobaczysz, że Power Query dodał krok:

    #"Wypełnienie w dół" = Table.FillDown(#"Poprzedni krok", {"Office Team"})
    

    Wyjaśnienie:

    • Table.FillDown to funkcja, która wypełnia luki.
    • {"Office Team"} oznacza, że dotyczy tylko tej kolumny.
    • #Poprzedni krok to odwołanie do wcześniejszej operacji (np. zmiana typu danych).

    Różnica między "Fill Down" a "Fill Up"

    • "Fill Down" (wypełnij w dół):
      • Bierze wartość z góry i uzupełnia null w dół, aż do następnej znanej wartości.
    • "Fill Up" (wypełnij w górę):
      • Bierze wartość z dołu i uzupełnia null w górę.

    Przykład dla "Fill Up":

    Employee IDNameOffice Team
    1Annanull
    2Bennull
    3ClaireTeam A
    4Davidnull

    Po "Fill Up":

    • Anna i Ben dostaną Team A (bo najbliższa wartość jest pod nimi).

    Kiedy "Fill Down" nie zadziała dobrze?

    1. Jeśli pierwszy wiersz ma null:

      • Nie ma wartości "z góry", więc luka pozostanie.
      • Rozwiązanie: Użyj najpierw "Fill Up", a potem "Fill Down".
    2. Jeśli dane są nieuporządkowane:

      • Przykład: Team A, null, Team B, null.
      • "Fill Down" da: Team A, Team A, Team B, Team B.
      • Ale jeśli logicznie powinno być: Team A, Team A, Team B, Team A (bo cykliczne grupy), to trzeba ręcznie poprawić dane.
    3. Gdy brakuje wielu wartości pod rząd:

      • Jeśli masz 10 null z rzędu, a dopiero 11-ta wartość jest OK, to wszystkie 10 pierwszych dostaną tę 11-tą wartość.
      • Czasem to błąd (np. w danych sprzedażowych).

    Alternatywny sposób: Wypełnianie przez kliknięcie PPM (prawym przyciskiem)

    Zamiast iść do karty "Transform":

    1. Zaznacz kolumnę.
    2. Kliknij prawym przyciskiem myszy na nagłówek kolumny.
    3. Wybierz "Fill" > "Down".

    To dokładnie to samo, co przez kartę "Transform", ale szybsze dla niektórych użytkowników.

    Podsumowanie

    • "Fill Down" uzupełnia null danymi z góry.
    • Idealne dla **hierarchicznych.
    • Używaj, gdy dane są poukładane chronologicznie lub hierarchicznie.
    • Pamiętaj o "Fill Up", jeśli pierwszy rekord ma null.
    • W trudnych przypadkach ręcznie popraw dane zamiast ślepo uzupełniać.

    Teraz wiesz już wszystko o tej magicznej funkcji! Przejdź do Power Query i wypróbuj na prawdziwych danych.

  5. User

    Streść mi w postaci pytań i odpowiedzi: "In the last video, we have set up our data model. So importing the data and also managing the relationships in the model view. And now it's time to create our first base measures in Power BI, which you can later use for different kinds of tax calculations. Before that, I highly encourage you if you have not done so far to save your file, so click on the Save disk or press ctrl s and just name it properly. I already did that and called it Power BI course file because this makes sure that all the progress we have done so far is saved, like importing, cleaning the data and also set up the model relationship. So next, let's actually start creating our measures. And for that, I first like to create a separate table. And this table will contain all our measures which we create during the course. So if you have taken the first master class course, you probably know how that works. If not, no worries because we do it together right now. So first I need to have some kind of dummy data so as my scaffold or placeholder. And I can do that by going under home and under home, there's the option to enter data manually. So this enter data option. So let's click it. And then here we can create our measures table. So let's give it a proper name. And for me I call it simply measures and then TBL. So I stick to my abbreviations and then I just enter a dummy value, like something like one. I think actually that you do not really have to add anything. You could also leave just the column and don't add any data. That should also work. But no, it doesn't matter. So let's click load to load this table in the model. So we just need to wait a little bit. And there's our table. So we have a measures table in here and now we can use this table in order to create our measures. So let's right click on the table and create a new measure. By the way, this new measure, of course, can also be created if you go under modeling there. There's also an option to create here a measure in power BI However, I personally prefer it by doing the right click on the table because this makes sure that I create the measure directly in the right table. Otherwise, if you do it from the ribbon bar, then you always create the measure. In this case, here in the table, the last table you have selected. You know that you can move measures around later on. But still, if I can do it immediately in the right table, why not doing this? So let's right click here and go to new measure. So click on it and then let's just zoom in and our first measure will be our total sales. So I'm referring to total sales. Sales is equal to. And then I personally like to go to new line pressing shift enter. Of course you can write it also in the same line. It's up to you. Just a personal preference of mine. And now for that, what we need is we need to iterate our sales table because in the sales table or in our case, the orders table, that's what we named them actually. We have various columns and two of those columns is the amount which has been sold to the customer and also the selling price which we have for the customer or which the customer paid us. So to get now the total sales, what we need to do is we need to go row by row through the orders table, multiply those two rows together and at the end we sum all of them up. It's kind of the same as in any other kind of tool like Excel, for instance. So for that we have so called iterator functions in Power BI and in Dax, and most iterator functions have an X at the end. This is how you can recognize, okay, that is an iterator and an iterator function always iterates over a table, meaning it goes row by row through the table and then it does some aggregation. So because we want to sum up those values, we use the sum X function. So sum X and then press enter. Or in this case, tab. And then you see here, if you press delete key, you can see here the description. So sum X is an iterator function. And one thing which all iterator functions have in common is they always have a table as the first argument because the function itself needs to know, okay, which table should I actually iterate over? So in this case it is our orders table so I can simply write orders. TBL And then for this table we need an expression. So this is what is evaluated for each row in the table. And because it has a context, in this case a row context for each table, we can then simply refer to the specific row in the column and the row so the cell inside the row. So that means if I search for any kind of column name in my orders table, I can find it. So if I search for a mount, for instance, you see that I can reference the orders table mount. And then I can simply multiply this using the star symbol by, in this case, the selling price. There's a selling price. Here it is. And then we can close the parentheses. And this is actually the formula we need. So one more time, it is an iterator function. We can clearly recognize this with the X at the end. So whenever you see this for formulas in power BI, you know, they are either real functions and if you know that a function is an iterator function, you also know that the first argument is always a table because the function needs to know which table should I actually iterate over. So meaning going row by row through this table and then evaluate something. So in this case, we have our iteration. And then of course, we need some kind of aggregation. So after this is evaluated for each of the rows when you have a sum. So we want to sum it up. And by the way, there are various iterator functions. There's also, for instance, a max X iterator function. There's a min x iterator function. There's an average X iterator function. So all these iterator functions do some kind of aggregations after they have evaluated this expression. So now enough said, Let's just evaluate that. Let's press enter. And now we have here our total sales measure. So now we can simply modify this table here, this message table, and convert it into a measures table. So proper measures table simply by right clicking on the column one or dummy column and then just delete it. So delete from model because now we don't need it anymore. We have the measure inside the table and as soon as we do this we delete it. Of course we are asked to. We Are we sure we want to do this? Yes, we are. So let's click on. Yes. And then you see immediately that now the message table gets converted because now it has this little calculator icons here. It also gets automatically put on the top. So it's only for structure. So it doesn't really change the performance, for instance, for our reports or anything like that. But still, I think this is really handy to have all the measures in one place and also have a visual indicator that this is actually where our measures are stored. Okay, So next, let's create a few additional basic measures. So for that I right click on the measures table one more time. Go to new measure in here. And now I'd like to calculate my total cost. So they are kind of similar. So if I search for total cost, I can equal shift enter to go to New line. And again, I want to iterate over a table. In this case it's one more time the orders table. So I'm using the sum X because also we want to sum up the costs to have the total cost. So there's the sum X function. And then in here we need the table. So let's go in here and say for the table it is our orders table. And for the expression one more time, we need the amount so I can reference the amount and we will multiply this in this case, not by the selling price. This is what we received, but by the purchasing price. This is what the customer paid or sorry, this is what not the customer paid. This is the selling price. This is what we paid to the supplier. So let's search for purchasing. Okay, here we go. And then let's just close the parentheses and we are good to go. So the the way this works is exactly the same as we just discussed with the total sales. So for now, I don't think I need to say anything and repeat myself. So let's actually press enter and evaluate that. And we also got the total cost. As soon as we have our base measures here. So these two, we can reference them. And that's quite easy because in order to calculate, for instance, the total profit, all we need to do is right click new measure and then say, this is our total profit is equal to and then I can simply reference those measures and say this is total sales. You can see that we can easily reference them and we subtract here the total cost like that. So that's why most often it's best to create your simple so in quotes, simple base measures first and then you can reference them in other formulas. Not only something well, quite easy like this one, but also more complex tax expression could also reference your base measures. So that's how you it's kind of a measure staking, if you want to call it this way. So now we have that press enter to evaluate that and it's also available. And finally, we would like to calculate also a margin and the profit margin. In our case, we keep it simple, is just total profit divided by total sales. So we can right click here, go to new measure. And then for this measure, this will be our profit margin. Profit margin is equal to and I go to New Line and then in here I say I would like to divide the total profit by the total sales. Now, of course, you could actually reference the measures directly again like that and divide it by the total sales. But in Power BI, it is best practice actually whenever you have a division to use the divide function because here you have the risk if you divide it like this way that you get some kind of division by zero error and this error would display it in your power reports. And if you want to avoid this by default, it's much safer to use the divide function because the divide function can handle these division by zero errors. So if you instead use divide divide, then you can see you have a numerator, a denominator, and you can also specify an alternate result. So you can see here it's called safe divide function with the ability to handle divide by zero case. That's exactly the point. So for the numerator I can use now total profit for the denominator. I can reference my total sales. Total sales like that. And then I could this is optional. Always remember if you have arguments in brackets, they are optional. You could specify the alternate result. If we leave it empty, then by default, in case a division by zero error occurs, then simply it displays blank. So nothing. Or if you want to have something alternative, you could also of course, put in quotes some kind of text, like no data or anything like that. It's up to you, right? Something like that. This will be one option. Or as I said, in my case, I just leave it blank so I can just close it here. And that also works. Okay, so let's actually execute this. So we got this as well. And now we can actually use this in our report. And I would always encourage you to check the measures, whether they are correct, because besides syntax errors, like for instance, this one, if I remove those two and if I would press the check box here, then of course I would get an warning here immediately that this actually is an error. But if I actually write a correct formula like that, total sales, of course I need to get rid of the parentheses like that. Here we go. Then, of course, the Dax formula might be correct, but still the value might be wrong, right? That's also a good practice actually, to have maybe a small little table just to check that the values are correct. So if I go, for instance, to my orders table in here, I have here a category. So if I check the category here and let's make this a little bit bigger. So the option, the formatting option currently is still not there. This will be available probably in one of the next updates, but for now I need to go to the View tab and then I also click the formatting. So it appears here on the sidebar and then I can go to the value section in here and just increase the size to let's say 14 like that so we can see this better. And now if we can go back in here and add either click on add data here and then choose from the measures table, the measures like this one, for instance, I could say I'd like to see the total cost click on it, then you would see it. I can also go in here, check the box on total profit. Then this will also add it to my visual. Or I could also directly drag and drop it in here. This would also work to add the visual to my day, to my visual. And of course, besides this, I also would like to see the profit margin. So one more time either click on add data or drag it directly in here like that. And by the way, of course you could also change the order simply by dragging this around. So place it maybe here on the second. Then of course, now the order is changed. Right? So quite easy to do this. What we can clearly see is that currently the formatting is not correct because this should be US dollars. So and this should be a percentage number. So the first one profit margin, this is only one measure, so I can easily fix it directly in here and go to percentage. And this would now update here the display. So what it looks like for the other ones, I could also do it the same way, select one and then specify here that I want dollar and the amount of decimal numbers. Or I could do this in bulk by simply going to the model view here and then just select the measure. So the first one I can hold my shift key and select the last one or I can hold Ctrl key and select individual ones and then I can go here under the formatting option and I have the option to go here directly to currency. I can do currency here and then also the currency format here I have various options. By default I could select here, I would like to see US dollars so I can select US dollar and then decimal number should be two. The 1000 separator separators currently are set to. Yes. Okay. So let's check that. Let's go back to the report view. And now you can see that here we can see the dollar symbol as well as two decimal numbers. And the 1000 separator is also included. Right. So this is a way to actually format your measures in bulk and might save you a little bit of time instead of doing it manually. Okay. So that's it. And those measures look correct. So let's actually drag maybe the total sales go here and drag it before the total cost so we can see that place it here. And if I can see 250, 1000 -142,000. So this is around 108,000. So that looks fine to me. And of course, if I subtract 850 from 1.3 million, that also should arrive at value like that. So the measures look correct. So we have checked that. Okay. And that's actually it for this video. So to recap, we have created our base measures in here. We checked the base measures, we formatted the base measures. And now we can get started from some additional time intelligence and other kinds of Dax calculations in the next couple of videos. So thanks a lot for watching. And of course, also for participating, because remember, the best and in my case, actually only way to learn things is to do it practically. So hopefully you follow along either during the video or now you pause the course and do this on your own. And remember, just in case anything went wrong, you can always go back to the video and then check what's missing. And by the time you get better and better and you learn how to do things.

    Now, after creating our base measures in the last video, now let's dive deeper into Dax and we get started with Time intelligence. Why is that? Because time intelligence is something which is very, very often asked in various business use cases. And you come across with time intelligence functions, probably in any kind of power BI report you build in your future. So that's why they are crucial and that's why it's good to learn and practice them. So for that, let me just maybe double click here and say this are actually our base measures, so let's call it base measures like that and then press control s to save it to my report. And then I go to new report page. Of course you can also continue on your first one, but I personally prefer to have a new one here and directly in here I name this time intelligence like that. So now we need some kind of table structure to evaluate whether our measures which we create now will be true and valid. So let's actually go in my first in my dates table in here and there. I have a date. And this date, if I select it, automatically, has a hierarchy attached to it, which you can see here. The date has a year, a quarter, a month and a day. And you can also see the hierarchy if you click here on the icon, because there there is the date hierarchy which gets created automatically for us. Now, the reason why it is created automatically is because the auto date time function is currently activated. So if you don't know, do not know about that. I covered this in my first course as well, but let's do a little recap. So by default, if you have not disabled it, if you go to file and under file in the options and settings and the options in here of your report, you would find that if you go inside the data load there on the time intelligence this is ticked to on so this is a checkbox and this is checked. If you want to learn more about it, you can either click on Learn More or click on this little or hover over this little information icon. But by default, what happens is if this is checked, then for each time or date dimension you have in your model. So for instance, here we have one in the dates table, but also we have one in the orders table because actually twice because we have the order date and the ship date. And also for those power BI under the hood, you might call it this way is creating dates tables, which allows us to work with hierarchies. Now, this is totally fine if you work with small models. The only time which this might actually become very problematic is if you have large models. So when you're dealing with huge models, for instance, an enterprise level and you have really a large data model, then I would recommend actually to untick this option because if you untick it, then Power BI is not creating those hierarchies automatically for you anymore. That's the negative part. But the positive part is that it also reduces your dataset size. So your whole model gets smaller and therefore more efficient. And especially if you have really large models with huge timeframes, then this can make a tremendous difference. So the model could really shrink down ten times and even more maybe. So that is why you want to uncheck this when you're dealing with large, large models. In our specific case, we have not to worry about that because our model is small and therefore there is no need to untick it. And that also is a little bit, well, tedious because if we untick it, we would have to create this hierarchy ourselves. I've shown you in my first course how to do this. It's quite easy, but still it's a little bit of work. And so why not using it directly in here? And as I said, by default, normally it is activated. So if you have no problems regarding the dataset, size and so on, then you can leave it checked. But if not, then I would recommend to uncheck it. So having said that, let's cancel this and then let's get started. So let's go in here and at first I want to get rid of the quota in the month level. So if I go in my view here, build a visual, I can just delete here the day level and I can also delete the quota and then I can make this a little bigger. And for the table visual currently still the formatting option is here, not available. In the near future there will be an icon which you can click to go directly to this formatting here, but right now it's missing. So this is why I can go in here and do the formatting myself in here. Just increase the font size to 14 again. So like that. So that's a little bit bigger. So far so good. That works. And by the way, if you want to restore this hierarchy, you can always right click here, switch it to date, and then switch it back to hierarchy or you drag the date in here in the column section, and then you have also your quota in your day level back just in case you want to or you need this. And also which I like to point out before we start with the calculation in Dax is that we currently do this for the table because I personally believe it's most easiest to see the calculation. And whether it's correct or not, if you have a table visual so you can see the real numbers, but this can also be applied to any kind of chart charting visuals like bar chart, line chart and other other kinds of charts which are available in power. BI I just wanted to mention this, so that's clear. Okay. So now let's actually visualize some of our base measures. So if I go in my measures table and select for instance, the total sales, so either tick the box or add it from here, then of course the data is there. That's fine. So the first calculation we would like to do together is a running sum or cumulative sum. I also did this in the first course. I know. So just in case you you have seen that. But in this case here we do it a little bit different. So you learn also something new because in power BI what's true and this is probably true for a lot of other well tools as well, is that they're always more than just one way to get to the solution, right? Just more than one way to skin the cat, right? I might say that. So let's actually explore this. So the running sum or cumulative sum is simply that we add the individual values so up for each new month, meaning in August, we have the sum of those two. In September we have the sum of those three and so on. So for that, let's actually do this and right click on the measure table here and create a new measure. And let's call this running. Total sales is equal to. And then I go to New Line in here and then I specify here my variable. So directly we get started with variables. So we define a variable using the var keyword. So var and then we need to give the variable a name. The name here will be current. Let's say this is the current date and the current date is referencing the current date in our selection here. So in this case, in this row, it's January 2022. In this row it would be July 2022 and so on. Right? So now is equal to equal to and we define this is the maximum of the dates table. And for the dates table, it's the date. So it should be I guess, this one here. So let's close this and let me get rid of that. So now we have actually removed it. Just this was a little disturbing to me. So let me actually close this first. So make this smaller. And now let's actually recreate the measure. So sorry for that right click. And now again, new measure. And now for the measure again. We say this is our running total sales is equal to and now go to New line and then let's say it's my variable so the variable can store in values for us. So either single expressions or tables can be stored in variables. So let's say our current date in here is the name of the variable and we say it's equal to the maximum max of the dates table and therefore the date this column here. And then we need to close it and then we go to New Line and then we specify here the return keyword return, and then we simply say for the return we want to calculate the total sales. So our measure total sales in a new context and this new context can be now created inside these filter arguments. So remember, calculate is the most powerful function in Dax because it allows to change the filter context. And just to mention this briefly, what is the filter context? The filter context is the context. So the filter which is applied in each visual meaning in here, for instance, the filter context for this line would be June 2022. And we need to modify this context because for the running total expression, of course we need to evaluate not only June but June, including all the prior months. So when we read June 20th, 22 here, we actually read all months, including June 20th, 22 and 2021, because the year also appeared before. So this is what we need to give it in our filter expression. And that's why we can reference here the dates table and the date, the specific date, and we say everything which is less, less or equal to our in this case, current date. So that's the variable which we have created. And power BI also is so nice that it gives us here also a visual indication that is screen here that this reference to this variable. So the value we stored in here. So now we have actually our running sum and now if we evaluate that, so press enter and now select the measure. Let's do the formatting directly in here. Let's choose dollar amount and let's say to press enter. So now we have actually something comparable to our sales here. And now let's actually drag this in the view drag and drop it. And now you can see that here we got now the running sum. So this value here is not the August 2021 anymore. It's simply the sum of those two in here. And then we got the sum of those three and here the sum of those four in here and so on. And you see that here at the end in the last year, in May 2022. Of course, now there is no no additional sales anymore. And that's why the value is repeating here in this value, of course, should be the same as the value here in the bottom. So this makes sure that our calculation is correct. So now what we'd like to do is actually first get rid of these repeating values in here. So because the end of the last sales month in this case is May 2023, there is no reason to actually show these values here as well. So for that we can actually modify our calculation. So if I go back to the running total sales in here and then I just need to modify this and I just need to check with, for instance, an if condition, whether actually we have total sales. Because the idea is if there is this is a blank value, there are no total sales, then also don't show this value. And for that we can actually wrap our calculate here if in an if condition. So we can say if in that case need parentheses and the logical test could be something like not and then is blank, there's an is blank function and then I can reference my total sales measure, total sales. So if the total sales are not blank like that, then if the result is true, evaluate what we've done here and if the result is false, then in case don't show anything and this can be done by blank like that, I can close it. And that's actually the formula. And before we evaluate that, let's actually select everything here, control C to copy it. And then I go to a little helper, which is my Dax formatter here. Dax format is a page by the SQL guys, which allows us to paste our Dax formula in here and like that and then we can just format it and it gives us a proper format for that. So like that. So in this case, I would not write my Dax function this way. I would not use all the spaces and so on, and that's why I like to paste it in here, just copy the formula and then go back to my report and just paste it inside like that. And you see that this is much easier to read. So then I just check it to evaluate that and you can see it already did that and now we can see there are no additional repeating values in here. So just end it in May because after the we do not have any sales data anymore. That's why it's currently not shown. And of course, if a new data comes now to our dataset and we got June data for instance, then of course there would also be a June value for the running total sales. So let's go back to the measure one more time. So this is the measure. And that's what I meant when I said that here. We could also write the formula in various ways, because here like this, not the negation of isblank total sales. You could also say if total sales UN equals blank, that would also work. So you don't have to write it exactly this way. There are various ways to do that. So and of course what we did in the first course, we did not use any variables. You could also reference the dates directly in here instead of using variables that would also be possible. So having said that, let's actually evaluate that again one more time. And we are good to go with the running total sales. Now, one thing which often comes to mind when people first calculate this is okay, that is interesting, but actually I'm much more interested in the running total sales for each year. So for something like total sales year to date, right? These kinds of expressions or quarter to date or month to date values, instead of having really the value in a running sum over all the timeframes we have. So the whole three year period in this case. So how can we reset this value so that it gets started here in July? Because July is the first month of data we have, but then it ends in December. So this is the max value to 60. And then here it restarts in January with the 76,000 and so on. So we don't want to continue here with the sum up we would like to restart here in January 22nd and also restart then in January 23rd in here. For that. We have various options, but to be quite convenient, actually there is an option to use a Dax expression which is exactly available for that, and that is the total year to date function. Meaning I could right click here on the measures table and then I can go to new measure in here. And now I go in here and say, Select that and call this. This is the running total sales and I will say YTD for year to date equal to. And now I go to New Line and here Power BI makes it really easy for us because all we need to do is reference the total year to date function. So total year to date. So year to date in this case like that. And actually I don't know why the year now it comes. Okay there it is. Here the. IntelliSense and now we can see it's totally up to date and now we can simply reference our sales total sales measure here and then we just need to give it the dates table. So in this case, it's dates, table date like that, and then we can close this and then this is our formula. So this means we want to calculate the running sum, but we want to reset with each new year. And if we reload that. So if I press the checkbox here, you see that it's also available in here. I can select it. I click here, the dollar symbol. I want to have two decimal numbers and then I also will add it to the visual. So go in here and just choose the running total sales year to date. And you see that we got exactly the same numbers as here, but with the difference that here in a new year it restarted. So we do not see the 336,000. Instead, we see that 76,000, which is the default value for January. And then, of course, it again sums up the values in here up to the end of this year in December, and then it restarts in January. And this is the total year to date function in here. You see, it has the same issue as the running total sales, also a cumulative sum function. So we also need to make sure that we simply check with an if condition, whether there is a value. So it works the same way. We can go in our formula. So let me take this and then I can wrap this in an if condition. So let's say if and then we check simply for the total sales and now let's do it differently. Let's say if total sales. So total sales is unequal. That's what I meant when I say there are various ways to do it. So we can specify unequal like that and then reference to blank. So if that's the case, then if this is true, then evaluate it. Otherwise return blank and close this. And again, I will just copy all of this control C to copy it. I go to my text formatter here I go to edit, and then I just paste it inside press format and then let's just see there's the result. Copy it. Go back to our report and just paste it inside. Press enter and we can see that now in here. It also stops here in May, right? There are no additional columns with empty total sales numbers here, for instance. Okay. So we got also this fixed. And just to add here, maybe because we have running total sales year to date, there's also a modification of this and let's create it so you can see that. So if I right click on measures table, go to new measure in here, then here I can specify this is total, let's say running total sales sales and then qtd for quarter to date equals. And then I go to new Line Shift, enter and say this is the total and they can see there's a total month to date, quarter to date in year to date. Now, the month to date here doesn't really make any sense in our case because we are currently already on a monthly level. So only if you show the data on a daily level then the month to date would make sense. Otherwise it would give us the exact same value as the normal formula for some x. For the total sales. So that's why the quarter to date we can see that. So let's actually show this and do that here with the total sales one more time. And by the way, you could also use total cost or total profit. I'm just using total sales as an example. So if I select it and then for the dates table here, I reference again the dates table date like this, and then I can close this and I can also evaluate it and press enter. And one more time I select it. I go to the formatting option here under the measure tools press also in this case, the auto set it to two two decimal numbers and then I go to my visual here and then I just drag it inside. Or you can also use this on object, the new one, this option here, and then go to add data and then choose here the measures and choose it from here the quarter to date. And you see that there are the values in here like that. And also for the date, we have the same issue as for the other ones. There is one additional value in here because the quarter ends, of course, in June, but we have no data for these ones. And you could do the same trick. We will just skip it for now. I'll leave this as an exercise for you to also wrap it in an if condition. Feel free to check out different ways to check whether the condition is true and just remove this from here. You already have seen two of them in the other two formulas we have created. So let me just drag it out a little bit, make it bigger so we can see this here. Right? And this is actually for these year to date functions. So as I said, there are three of them and they are quite handy because they're really, really easy to use. The only thing which is required, as you've seen various times, is the proper dates table. And this is why whenever you deal with time intelligence, it's really, really important that you have a separate date table which contains all the dates. There are no missing values, no duplication, and this can be used in the time intelligence, and this makes sure that these formulas are validated correctly."

  6. Meta Assistant

    Oto streszczenie treści w postaci pytań i odpowiedzi:

    Pytanie 1: Dlaczego warto zapisywać plik Power BI regularnie podczas pracy? Odpowiedź: Aby nie stracić postępów pracy (np. import danych, czyszczenie danych, relacje w modelu). Kliknij ikonę dysku lub użyj Ctrl + S i nadaj plikowi czytelną nazwę (np. "Power BI course file").

    Pytanie 2: Jak utworzyć tabelę na miary (Measures Table) w Power BI? Odpowiedź:

    1. Przejdź do Home > Enter Data.
    2. Nazwij tabelę (np. "Measures TBL").
    3. Wpisz dowolną wartość (np. "1") lub pozostaw pustą kolumnę.
    4. Kliknij Load, aby załadować tabelę do modelu.

    Pytanie 3: Dlaczego lepiej tworzyć miary (measures) przez PPM (prawy przycisk myszy) na tabeli? Odpowiedź: Kliknięcie PPM na tabeli "Measures TBL" gwarantuje, że nowa miara zostanie utworzona w odpowiedniej tabeli. Jeśli zrobisz to z górnego paska (Modeling > New Measure), miara powstanie w ostatnio wybranej tabeli (co może być błędne). Potem wprawdzie możesz przenieść miarę, ale wygodniej zrobić to od razu dobrze.

    Pytanie 4: Jak działa formuła SUMX w DAX? Czy to iterator function? Odpowiedź: Tak, SUMX to iterator function (rozpoznasz po X na końcu nazwy). Działa tak:

    1. Pierwszy argument to tabela do iteracji (np. Orders TBL).
    2. Drugi argument to wyrażenie liczone dla każdego wiersza.
    3. SUMX przechodzi wiersz po wierszu przez tabelę, oblicza wyrażenie, a potem sumuje wyniki.

    Przykład:

    Total Sales = SUMX(Orders TBL, Orders TBL[Amount] * Orders TBL[Selling Price])
    

    Wyjaśnienie:

    • Dla każdego zamówienia (Orders TBL) mnoży Amount (ilość) przez Selling Price (cenę sprzedaży).
    • Potem sumuje wszystkie wyniki.

    Pytanie 5: Jak zamienić tabelę "Measures TBL" z "dummy column" na właściwą tabelę miar? Odpowiedź:

    1. Kliknij PPM na kolumnę "1" (lub inną dummy-kolumnę).
    2. Wybierz "Delete from model".
    3. Potwierdź ("Yes").

    Efekt:

    • Tabela nie zniknie, ale zmieni ikonę na kalkulator (oznacza tabelę z miarami).
    • Automatycznie przesunie się na górę modelu (to tylko wizualna konwencja, bez wpływu na wydajność).

    Pytanie 6: Jak stworzyć miarę Total Cost analogicznie do Total Sales? Odpowiedź: Użyj ponownie SUMX, ale tym razem mnożąc Amount przez Purchasing Price:

    Total Cost = SUMX(Orders TBL, Orders TBL[Amount] * Orders TBL[Purchasing Price])
    

    Różnica:

    • W Total Sales używamy Selling Price (co klienci nam zapłacili).
    • W Total Cost używamy Purchasing Price (co my zapłaciliśmy dostawcom).

    Pytanie 7: Jak obliczyć Total Profit mając już Total Sales i Total Cost? Odpowiedź: Wystarczy odjąć miary:

    Total Profit = [Total Sales] - [Total Cost]
    

    Dlaczego tak?

    • Nie powtarzamy kodu (DRY - Don't Repeat Yourself).
    • Jeśli zmieni się logika Total Sales lub Total Cost, Total Profit automatycznie zaktualizuje wyniki.

    Pytanie 8: Jak napisać miarę Profit Margin bezpiecznie (unikając dzielenia przez zero)? Odpowiedź: Użyj funkcji DIVIDE:

    Profit Margin = DIVIDE([Total Profit], [Total Sales])
    

    Dlaczego DIVIDE?

    • Zwykłe [Total Profit] / [Total Sales] może dać błąd przy zerowych sprzedażach.
    • DIVIDE ma wbudowaną obsługę division by zero:
      • Jeśli mianownik = 0, zwraca blank (lub opcjonalnie wybrany tekst, np. "Brak danych").

    Pełna składnia:

    Profit Margin = DIVIDE([Total Profit], [Total Sales], "No Data")
    

    Pytanie 9: Jak sprawdzić, czy miary (Total Sales, Total Cost itd.) zwracają poprawne wartości? Odpowiedź:

    1. Utwórz Table Visual w raporcie.
    2. Dodaj kolumnę Category z Orders TBL.
    3. Wrzuć na visual miary (np. Total Sales, Total Cost, Total Profit, Profit Margin).
    4. Zwiększ rozmiar tekstu (View > Formatting > rozmiar 14).
    5. Sprawdź wyniki:
      • Np. Total Sales ≈ 1.3 mln, Total Cost ≈ 850 tys. → Total Profit ≈ 450 tys. (powinno się zgadzać).

    Pytanie 10: Jak sformatować wszystkie miary naraz (np. do waluty USD z 2 miejscami po przecinku)? Odpowiedź:

    1. Przejdź do Model View.
    2. Zaznacz wszystkie miary (Shift lub Ctrl + kliknięcie).
    3. W Measure Tools (górny pasek) ustaw:
      • Format: Currency.
      • Symbol: USD.
      • Decimal places: 2.
      • Tysiące separator: ,

    Wtedy wszystkie wybrane miary przyjmą ten format naraz (oszczędność czasu).

    Pytanie 11: Czym jest "Running Total" (suma krocząca, suma skumulowana)? Odpowiedź: To miara, która sumuje wartości narastająco w czasie:

    • Styczeń: 100
    • Luty: 100 + 150 = 250
    • Marzec: 100 + 150 + 200 = 450
    • itd.

    W DAX robimy to tak:

    Running Total Sales = 
    VAR CurrentDate = MAX('Dates TBL'[Date])
    RETURN
        CALCULATE(
            [Total Sales],
            'Dates TBL'[Date] <= CurrentDate
        )
    

    Wyjaśnienie:

    • VAR CurrentDate zapamiętuje datę aktualnego wiersza (np. 15.02.2022).
    • CALCULATE zmienia kontekst filtra: bierze wszystkie daty wcześniejsze lub równe CurrentDate.

    Pytanie 12: Jak ukryć "Running Total", jeśli nie ma sprzedaży (Total Sales jest pusty)? Odpowiedź: Owiń CALCULATE w IF:

    Running Total Sales = 
    IF(
        NOT ISBLANK([Total Sales]),
        VAR CurrentDate = MAX('Dates TBL'[Date])
        RETURN
            CALCULATE(
                [Total Sales],
                'Dates TBL'[Date] <= CurrentDate
            ),
        BLANK()
    )
    

    Lub prościej:

    IF([Total Sales] <> BLANK(), [... reszta kodu ...], BLANK())
    

    Dlaczego?

    • Jeśli brak sprzedaży, miara zwraca puste pole (BLANK()), a nie zero.

    Pytanie 13: Co to jest TOTALYTD (Year-To-Date, suma od początku roku)? Odpowiedź: To funkcja DAX, która automatycznie resetuje sumę kroczącą co roku:

    Running Total Sales YTD = TOTALYTD([Total Sales], 'Dates TBL'[Date])
    

    Działa tak:

    • Styczeń 2022: 100
    • Grudzień 2022: suma od stycznia do grudnia 2022
    • Styczeń 2023: reset do 50 (nie dodaje starego 2022)
    • Grudzień 2023: suma od stycznia do grudnia 2023

    Pytanie 14: Jak napisać TOTALQTD (Quarter-To-Date, suma od początku kwartału)? Odpowiedź: Analogicznie:

    Running Total Sales QTD = TOTALQTD([Total Sales], 'Dates TBL'[Date])
    

    Działa tak:

    • 01.01.2022: 10
    • 02.01.2022: 10 + 20 = 30
    • 01.04.2022 (nowy kwartał): 50 (reset)
    • 02.04.2022: 50 + 30 = 80
    • itd.

    Pytanie 15: Dlaczego potrzebujemy odpowiedniej tabeli dat (Dates TBL) dla Time Intelligence? Odpowiedź:

    • Funkcje TOTALYTD, TOTALQTD wymagają ciągłej, niezlustrowanej tabeli dat.
    • Bez niej DAX nie wie, co to jest "rok", "kwartał", "miesiąc".
    • Auto Date/Time (domyślnie włączone w Power BI) tworzy ukrytą tabelę dat za kulisami, ale:
      • Dla małych modeli OK.
      • Dla dużych modeli (enterprise) lepiej wyłączyć (File > Options > Data Load), bo zmniejsza to rozmiar modelu nawet 10-krotnie.

    Teraz wiesz już wszystko o podstawowych miarach i Time Intelligence w Power BI! Czas na praktykę.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.