Přeskočit navigaci | Přeskočit na novinky
     

Jste zde: Úvodní stránka » excel » power-bi » Jak-na-relace-v-Power-BI-teorie
Microsoft Excel logo

Jak na relace v Power BI - teorie

Videokurzy Excel

Ve spolupráci se SEDUO jsem vytvořil několik videokurzů:

Jak fungují relace a jak je využít v Power BI aneb další článek z připravovaného seriálu Jak na Power BI od A do Z.

Úvodem do relací

Při práci s tabulkami často zjistíte, že mezi tabulkami existují vzájemné vazby (relace). Nebo-li tabulky jsou na sobě závislé. Jak fungují závislosti a jak je můžete následně využít v Power BI se dozvíte v tomto článku:


Úvodem co je relace

Relace je propojení dvou (a více) tabulek. Kdy mezi dvěma tabulkami je vazba na základě klíče (speciální jednoznačný kód). Následně na základě hodnoty z jedné tabulky můžete dohledat hodnoty v tabulce drruhé.

Prakticky potřebujete propojit dvě tabulky. Kdy máme dvě tabulky - Výrobky a Prodeje. Předpokládejme, že:

  • Tabulka Výrobky obsahuje všechny námi vyráběné výrobky. Kdy tabulka obsahuje sloupce: Název výrobku, Váha výrobku, Cena, Primární klíč (speciálmní jedinečné číslo - bude vysvětleno za chvílí).
  • Tabulka Prodeje obsahuje sloupce týkající se prodejů, rok prodeje, počet prodejů, ze kterého sladu se výrobek expeduje a cizí klíč (odkaz na onen primární klíč se kterým daný prodej souvisí - podrobněji bude vysvětleno za chvílí)

Aby šli tabulky propojit potřebujeme, aby tabulky obsahovali:

  • Primární klíč - unikátní číslo, kód, který se neopakuje a dle jeho kódu lze konkrétní zaznam (řádek v tabulce, takto se dá tento řádek přímo vybrat). V praxi jde například o IČO, DIČ (dle tohoto čísla zjistíte přímo daného podnikatele), možná rodné číslo (i když u rodného čísla dle některých informamcí, nejde až tak o unikátní číslo, že u starších osob se mohou najít dvě stejná rodná čísla) atd. V ukázkové tabulce půjde o sloupec ID.
  • Cizí klíč - provázaní druhé tabulky na primární klíč - neboli má vztah na primární klíč z první tabulky. Neboli když dojde k prodeji určitého výrobku (stačí se odkázat na ono ID - následně, již víte ostatní údaje z tabulky Výrobky - ono Idčko je unikátní a představuje jeden konkretní výrobek, ač jeho prodejů může být více - může se opakovat) a do tabulky Prodeje se k cizímu klíči doplní kód roku prodeje, počet prodaných kusů, atd.

Referenční integrita - nebo-li pomocí primárních a cizích klíčů tedy umožňují spojování tabulek. Kdy se přesně ví, které záznamy jsou spolu spojeny.

Power Bi  - relace

Najednou jsou tabulky přehlednější a lépe se v nich orientuje. Jako například výše uvedená ukázky, kdy vím, kdo které knihy přečetl.

Poznámka: O relacích je spousta teorie, koho zajíma, tak si rád dohledá.

Jak mohou vypadat tabulky

Pro praktickou ukázku budu využívat několik tabulek. Jde jen o pár záznamu, aby bylo jednak ukázano jak tabulky propojit a jak následně využívat další možnosti výpočtu, hlavně využitím DAX (možná již využíváte v Excel, tak vás potěším funguje stejně, jen se nepřiřazuje pomocí := ale jen = ale PowerBi zvládne : odstranit automaticky ;) ). Tabulky jsou:

  • Výrobky ID (jde o primární klíč), Jméno výrobku, Kategorie (cizí klíč pro Kategorií), Hmotnost, Cena
  • Prodeje ID (Cizí klíč pro tabulku Výrobky), Kód roku (cizí klíč identifikujíci rok prodeje - rok lze zjistit přímo z kódu, ale pro ukázku tvorby DAX funkcí ještě existuje tabulka Roky jako další možnost určení roku prodeje), Počet prodejů, Cena Pomoc (jde o pomocný sloupec pro kontrolu, prakticky je cena v tabulce Výrobky, je zde pro kontrolu a ukázku výstupu v reportech a pro tvorbu DAX)
  • Kategorie Sloupec Kategorie (primární klíč specifikující kategorií), Odpovědnost, Sklad (cizí klíč pro tabulku Sklad - kde je fyzické umístění skladu, město, ve kterém je zboží umístěno)
  • Sklad Sloupce Sklad (Primární klíč pro identifikaci konkretného skladu), Město
  • Roky Sloupec Kód roku (primární klíč identifikující rok prodeje), Rok (konkrétní rok prodeje, pro ukázkové výpočty - jinak lze zjistit i z kódu roku za využití textových funkcí - sloupec opět z praktických důvodů)

Ukázka tabulek

Power Bi  - relace - ukázky tabulek pro další práci

U tabulky Prodeje je záměrně navíc pomocný sloupec CenaPomoc, který budu v dalších článcích používat jako kontrolní pomůcku (neboli, berte jako by v tabulce sloupec nebyl).

Tabulky jsou záměrně malé (neobsahují mnoho sloupců a řádků), ale lze na nich ukázat soustu možností jak tabulky propojit, zobrazit a hlavně jak provádět measure (vlastní míry / výpočty s využitím jazyka DAX). A také rychle zkontrolovat, zda je váš výpočet správný. Ono hlavně při použivání DAX nemusí být na první pohled pochopitelný princip některých funkcí (SUM vs SUMX, RELATED vs RELATEDTABLE atd.).

Vazby mezi tabulkami

Jednotlivé tabulky máte, teď je jen propojit. Při propojení existují vazby 1:1 (speciální vazba, více informaci v přípravě), 1:n (což jsou náše případy, kdy jednomu primárnímu klíči může odpovídat více záznamu v další tabulce - cizí klíč), N:M (tuto vazbu je potřeba změnit na N:1 a 1:M (což již přesahuje rámec tohoto článku, proto se touto vazbou zatím v tomto článku zabývat nebudu).

Vazba 1:N - nebo-li jednomu Id (sloupec s názvem Id - jde o primární klíč v tabulce Výrobky) odpovídá (může odpovídat) více záznamu v druhé tabulce (tabulka Prodeje). Nebo-li pokud byl výrobek prodávám ve více letech, každý rok má v tabulce prodeju jeden záznam, kde ve sloupci Id je odkaz na Id z tabulky Výrobky. Tedy hodnota ve sloupci Id (cizí klíč v tabulce Prodeje) se v daném sloupci může opakovat vícekrát (vím pak o kolik šlo prodejů).

Power Bi  - relace - propojené tabulky

V ukázce u tabulky Prodeje záměrně chybí pomocný sloupec CenaPomoc, který budu v dalších článcích používat jen jako kontrolní pomůcku (tak abych nemátl).

Jak vypadá velká tabulka

Nemáte-li k dispozici vazby, můžete tabulku, díky vazbám přepsat a spojit do jedné tabulky (například v Excel využitím funkce SVYHLEDAT). I v tomto případě se zdá tabulka malá (některé sloupce jsou 2x, protože jsou v jiných tabulkách), aby šlo si v rámci naších testu si pomocí této tabulky naše výpočty, reporty ručně zkontrolovat, hlavně, zda se v DAX výpočty provedly správně.

Power Bi  - relace

Některé záznamy nemusí mít svého protivníka (tohle by s využitím funkce SVYHLEDAT se v Excel provést nedalo). Nebo-li mohu mýt výrobky které se zatím neprodaly, mít k dispozici číselník (opět se dozvíte, kde a jak využít), kdy již máme připravenou pomůcku na další roky prodejů, například 2021, když máme rok 2020, sklad který se začne používat, novou kategorií, atd.

Jak vypadají vazby v Power BI

V přechozím kroku jsme se podívaly na vazby v klasické podobě (jako obrázky s vazbami). Pokud tabulky převedete do Power BI můžete je propojit přímo zde (pokud máte tabulky vytvořeny správně, mnohdy se v Power BI automaticky propojí), jak na propojení bude popsáno v samostatném článku, pro doplnění článku jen ukázka, jak vypadá spojená tabulka přímo v Power BI.

Power Bi  - relace

Na co se mohu tabulky zeptat - využití vazeb

Tabulky máte propojené a teď, můžete provádět dotazy (tady je nejtěžší úkol, umět položit správný dotaz). Na pár příkladech ukáži a následně v dalších článcích se podíváme jak prakticky provedeme přímo v Power BI.

  • Jak na počet prodejů dle názvu výrobku
  • Počet prodejů dle kategorie
  • Počet prodejů dle skladu a roku
  • Hledám obrat na základě kriteria název
  • ... dotazy jsou na vás

Jak na počet prodejů dle názvu výrobku

Hledám: Počet prodejů (tabulka Prodeje, sloupec Počet prodejů) výrobku "Název 2" (tabulka Výrobky, sloupec Jméno výrobek)

Postup: V tabulce Výrobky najdu výrobek, zjistím primární klíč, neboli unikátní ID výrobku a v tabulce Prodeje díky cizímu klíči si spočtu hodnoty ve sloupci prodeje jen u záznamu, které odpovídají tomuto klíči (v ukázce jde o ID = 2).

Výsledek: 6

Ukázka v propojené tabulce:

Power Bi  - relace - jak bude vypadat řešení

Kontrola z velké tabulky: Opravdu to sedí? Mohu zkontrolovat ve velké tabulce, jen vyfiltruji hledaná název výrobku.

Power Bi  - relace - kontrola z velké tabulky

Počet prodejů dle kategorie

Hledám: Počet prodejů (tabulka Prodeje, sloupec Počet prodejů) v kategorií A K_A (v tabulce Kategorie sloupec Kategorie)

Postup: V tabulce Kategorie najdete primární klíč z něj v tabulce výrobky všechny primární klíče záznamů, které jsou v dané kategorií a nakonec v prodejích všechny prodeje, které odpovídají daným Id hodnotám a jen spočtete prodeje.

Výsledek: 14

Ukázka v propojené tabulce:

Power Bi  - relace - jak bude vypadat řešení

Kontrola z velké tabulky:

Power Bi  - relace - kontrola z velké tabulky

Počet prodejů dle skladu a roku

Hledám: Počet prodejů (tabulka Prodeje, sloupec Počet prodejů) sklad v Brně (v tabulce Sklad sloupec Město) v roce 2020 (v tabulce Roky sloupec Rok)

Postup: Hledám podobně jako v předchozím případě, ale dotazy jsou dva, tak nejprve najdu všechny prodeje, které se realizovali přes brněnský sklad (jen se prokoušete) a následně si vyhledáte prodeje pro daný rok, a výsledek je průsečík prodejů dle skladu a roku.

Výsledek:2

Ukázka v propojené tabulce:

Power Bi  - relace - jak bude vypadat řešení

Kontrola z velké tabulky:

Power Bi  - relace - kontrola z velké tabulky

Hledám obrat na základě kriteria název

Hledám: Obrat = Počet(tabulka Prodeje, sloupec Počet prodejů) * cena výrobku (tabulka Výrobky, sloupec Cena) výrobku "Název 2" (tabulka Výrobky, sloupec Jméno výrobek)

Postup: Víte-li, že obrat je cena * množství, tak jen "vyfiltrujete" (jak zjistit prodeje dle názvu víte z prvního příkladu) záznamy na základě splněných kritérií, ale pro výpočet budete potřebovat z tabulky Výrobky "přidat" do tabulky Prodeje (opět v samostatném článku, jak na to při tvorbě reportů) cenu z vybraných výrobků. (na tyto výpočty se nádherně hodí DAX, ale o tom v samostatném článku). Což je cíl ke kterému se chcete dopracovat.

Výsledek: 120

Ukázka v propojené tabulce:

Power Bi  - relace - jak bude vypadat řešení

Kontrola z velké tabulky:

Power Bi  - relace - kontrola z velké tabulky

Další využití v DAX

Budete-li mít tabulky propojeny můžete využívat tvorbu measuare přímo v Power BI za využití jazyka DAX, hlavně funkce:

  • CALCULATE
  • FITER
  • RELATED
  • RELATEDTABLE
  • SUM vs SUMX
  • ...

Související články

V přípravě

Microsoft Excel VBA - stahuj logo

Ke stažení

Soubor ke stažení zdarma je v přípravě. Podpořit zveřejnění můžete podporou na Patreon.


Závěrem

Napadá vás doplnění a vylepšení, můžete zmínit v článku.

Článek byl aktualizován: 19.09.2020 11:02

Odměna

Ušetřil vám tento web čas, peníze? Pomohl vyřešit problém? Jste ochotni poskytnout symbolickou odměnu na další rozvoj? Vybrte si formu odměny, která vám vyhovuje.


Pavel Lasák - autor webu

Pavel Lasák

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 15 let. Zdarma je zde přes 1.000 návodu, tipů a triků, včetně přes 250 různých šablon, sešitů.

   Pavel Lasák LinkedIn Profil    Pavel Lasák Google+ Profil    Pavel Lasák facebook Profil    Pavel Lasák twitter Profil


Komentáře zatím nejsou

Můžete být prvními co zanechají smysluplný komentář.







Sdílejte

Pomohl Vám návod?
Sdílejte na Facebooku, G+
LinkedIn...

Nové články


Reklama


TOPlist Licence Creative Commons webarchiv rss XML

Stránky o MS Office (Excel) produktu společnosti Microsoft. Neslouží jako technická podpora.
| Email na autora: pavel.lasak@gmail.com | Copyright © : Pavel Lasák 2004 - 2021 |