Kontekstas leidžia atlikti dinaminę analizę, kai formulės rezultatai gali keistis, kad atspindėtų dabartinę eilutę arba langelio pasirinkimą, taip pat visus susijusius duomenis. Konteksto supratimas ir efektyvus konteksto naudojimas yra labai svarbūs kuriant našias formules, dinamines analizes ir formulių trikčių diagnostiką.
Šioje sekcijoje apibrėžiami skirtingi konteksto tipai: eilutės kontekstas, užklausos kontekstas ir filtro kontekstas. Paaiškinama, kaip apskaičiuojamasis stulpelių ir "PivotTable" formulių kontekstas vertinamas.
Paskutinėje šio straipsnio dalyje pateikiami saitai su išsamiais pavyzdžiais, iliustruojančiais, kaip formulių rezultatai keičiasi pagal kontekstą.
Konteksto supratimas
Power Pivot formules gali paveikti "PivotTable" taikomi filtrai, ryšiai tarp lentelių ir formulėse naudojami filtrai. Kontekstas leidžia atlikti dinaminę analizę. Kuriant ir šalinant formulių triktis svarbu suprasti kontekstą.
Yra skirtingų tipų kontekstas: eilutės kontekstas, užklausos kontekstas ir filtro kontekstas.
Eilutės kontekstą galima laikyti "dabartine eilute". Jei sukūrėte apskaičiuojamąjį stulpelį, eilutės kontekstą sudaro kiekvienos atskiros eilutės reikšmės ir su dabartine eilute susijusios stulpelių reikšmės. Taip pat yra kelios funkcijos (EARLIER ir EARLIEST), kurios gauna reikšmę iš dabartinės eilutės ir tą reikšmę naudoja atliekant operaciją visoje lentelėje.
Užklausos kontekstas nurodo duomenų poaibį, kuris netiesiogiai sukuriamas kiekvienam "PivotTable" langeliui, atsižvelgiant į eilučių ir stulpelių antraštes.
Filtro kontekstas yra kiekviename stulpelyje leistinų reikšmių rinkinys, pagrįstas eilutėje taikomais arba formulėje filtravimo išraiškų apibrėžtais filtro apribojimais.
Eilutės kontekstas
Jei sukuriate formulę apskaičiuotame stulpelyje, tos formulės eilutės kontekste yra reikšmės iš visų dabartinės eilutės stulpelių. Jei lentelė susijusi su kita lentele, joje taip pat yra visos tos kitos lentelės reikšmės, susijusios su dabartine eilute.
Pvz., tarkime, kad sukuriate apskaičiuojamąjį stulpelį= [Transportavimo mokestis] + [Mokesčiai],
, kuris sudeda du stulpelius iš tos pačios lentelės. Ši formulė veikia kaip formulės "Excel" lentelėje, kuri automatiškai nurodo tos pačios eilutės reikšmes. Atkreipkite dėmesį, kad lentelės skiriasi nuo diapazonų: negalite nurodyti reikšmės iš eilutės prieš dabartinę eilutę naudodami diapazono notaciją ir negalite nurodyti jokios savavališkos atskiros reikšmės lentelėje arba langelyje. Visada turite dirbti su lentelėmis ir stulpeliais.
Eilutės kontekstas automatiškai seka ryšius tarp lentelių, kad nustatytų, kurios susijusių lentelių eilutės yra susietos su dabartine eilute.
Pavyzdžiui, ši formulė naudoja funkciją RELATED, kad gautų mokesčių reikšmę iš susijusios lentelės, atsižvelgiant į regioną, į kurį buvo išsiųstas užsakymas. Mokesčio reikšmė nustatoma naudojant dabartinės lentelės regiono reikšmę, peržiūrint regioną susijusioje lentelėje, tada gaunant to regiono mokesčio tarifą iš susijusios lentelės.
= [Transportavimo mokestis] + RELATED('Regionas'[TaxRate])
Ši formulė tiesiog gauna dabartinio regiono mokesčio tarifą iš lentelės Regionas. Jums nereikia žinoti arba nurodyti rakto, kuris sujungia lenteles.
Kelių eilučių kontekstas
Be to, DAX apima funkcijas, kurios kartoja skaičiavimus lentelėje. Šios funkcijos gali turėti kelias dabartines eilutes ir dabartinį eilučių kontekstą. Programavimo terminais galite kurti formules, kurios kartojasi per vidinį ir išorinį ciklą.
Tarkime, jūsų darbaknygėje yra lentelė Produktai ir Lentelė Pardavimas . Galbūt norėsite eiti per visą pardavimo lentelę, kurioje yra daug operacijų, susijusių su keliais produktais, ir vienoje operacijoje rasti didžiausią kiekvienam produktui užsakytą kiekį.
Programoje "Excel" šiam skaičiavimui reikalingos tarpinės suvestinės, kurias reikėtų perkurti pasikeitus duomenims. Jei esate galingas "Excel" vartotojas, galbūt galėsite kurti masyvo formules, kurios galėtų atlikti užduotį. Taip pat sąryšinių duomenų bazėje galite rašyti įdėtuosius antrinius žymėjimą.
Tačiau naudodami DAX galite sukurti vieną formulę, kuri pateikia teisingą reikšmę, o rezultatai automatiškai atnaujinami, kai į lenteles įtraukiate duomenų.
=MAXX(FILTER(Sales,[ProdKey]=EARLIER([ProdKey])),Sales[OrderQty])
Išsamias šios formulės gidas pateiktas ankstesniame skyriuje.
Trumpai tariant, funkcija EARLIER saugo prieš dabartinę operaciją atliktos operacijos eilutės kontekstą. Funkcija visą laiką atmintyje saugo du konteksto rinkinius: vienas konteksto rinkinys nurodo dabartinę vidinio formulės ciklo eilutę, o kitas konteksto rinkinys nurodo dabartinę formulės išorinio ciklo eilutę. DAX automatiškai pateikia reikšmes tarp dviejų ciklų, kad galėtumėte kurti sudėtingas agreguotas reikšmes.
Užklausos kontekstas
Užklausos kontekstas nurodo duomenų, kurie netiesiogiai gauti naudojant formulę, poaibį. Kai išplečiate matą ar kitą reikšmės lauką į "PivotTable" langelį, Power Pivot modulis patikrina eilučių ir stulpelių antraštes, duomenų filtrus ir ataskaitų filtrus, kad nustatytų kontekstą. Tada Power Pivot atlieka reikiamus skaičiavimus, kad užpildytų kiekvieną "PivotTable" langelį. Nuskaitytų duomenų rinkinys yra kiekvieno langelio užklausos kontekstas.
Kadangi kontekstas gali keistis priklausomai nuo formulės vietos, formulės rezultatai taip pat keičiasi atsižvelgiant į tai, ar naudojate formulę "PivotTable" su daug grupavimų ir filtrų, ar apskaičiuotame stulpelyje be filtrų ir minimalaus konteksto.
Tarkime, kad sukūrėte šią paprastą formulę, kuri sumuoja reikšmes stulpelyje Pelnas lentelėje Pardavimas :=SUM('Pardavimas'[Pelnas]).
Jei šią formulę naudojate apskaičiuojamajame stulpelyje lentelėje Pardavimas , formulės rezultatai bus tokie patys visoje lentelėje, nes formulės užklausos kontekstas visada yra visas lentelės Pardavimas duomenų rinkinys. Jūsų rezultatai bus pelnyti iš visų regionų, visų produktų, metų ir t. t.
Tačiau paprastai nenorite matyti to paties rezultato šimtus kartų, bet vietoj to norite gauti konkrečių metų, konkrečios šalies ar regiono, konkretaus produkto ar tam tikro jų derinio pelną, o tada gauti bendrąją sumą.
"PivotTable" lengva pakeisti kontekstą įtraukiant arba pašalinant stulpelių ir eilučių antraštes bei įtraukiant arba šalinant duomenų filtrus. Galite sukurti mate panašią formulę, o tada ją nuvilkti į "PivotTable". Kai įtraukiate stulpelių arba eilučių antraštes į "PivotTable", pakeičiate užklausos kontekstą, kuriame įvertinimas. Rūšiavimo ir filtravimo operacijos taip pat veikia kontekstą. Todėl ta pati formulė, naudojama "PivotTable", įvertinama skirtingame kiekvieno langelio užklausos kontekste.
Filtro kontekstas
Filtro kontekstas įtraukiamas, kai nurodote stulpelio arba lentelės leistinų reikšmių rinkinio filtro apribojimus, formulėje naudodami argumentus. Filtro kontekstas taikomas virš kitų kontekstų, pvz., eilutės konteksto arba užklausos konteksto.
Pavyzdžiui, "PivotTable" apskaičiuoja kiekvieno langelio reikšmes pagal eilučių ir stulpelių antraštes, kaip aprašyta ankstesniame užklausos konteksto skyriuje. Tačiau matuose arba apskaičiuojamuosiuose stulpeliuose, kuriuos įtraukiate į "PivotTable", galite nurodyti filtro reiškinius, kad galėtumėte valdyti reikšmes, kurias naudoja formulė. Taip pat galite pasirinktinai išvalyti konkrečių stulpelių filtrus.
Daugiau informacijos apie tai, kaip kurti filtrus formulėse, žr. Filtravimo funkcijos.
Pavyzdžių, kaip išvalyti filtrus norint sukurti bendrąsias sumas, žr. ALL.
Pavyzdžių, kaip pasirinktinai išvalyti ir taikyti filtrus formulėse, ieškokite funkcija ALLEXCEPT.
Todėl turite peržiūrėti matų ar formulių, naudojamų "PivotTable", apibrėžimą, kad suprastumėte filtro kontekstą interpretuodami formulių rezultatus.
Konteksto nustatymas formulėse
Kai kuriate formulę, Power Pivot, skirta "Excel", pirmiausia patikrina bendrąją sintaksę, tada patikrina pateiktų stulpelių ir lentelių pavadinimus pagal galimus esamo konteksto stulpelius ir lenteles. Jei Power Pivot neranda formulėje nurodytų stulpelių ir lentelių, gausite klaidos pranešimą.
Kontekstas nustatomas, kaip aprašyta ankstesniuose skyriuose, naudojant galimas darbaknygės lenteles, ryšius tarp lentelių ir visus pritaikytus filtrus.
Pavyzdžiui, jei ką tik importavote duomenų į naują lentelę ir netaikėte jokių filtrų, visas lentelės stulpelių rinkinys yra dabartinio konteksto dalis. Jei turite kelias lenteles, susietas ryšiais, ir dirbate su "PivotTable", kuri buvo filtruojama įtraukiant stulpelių antraštes ir naudojant duomenų filtrus, kontekstas apima susijusias lenteles ir visus duomenų filtrus.
Kontekstas yra efektyvi sąvoka, dėl kurios gali būti sunku šalinti formulių triktis. Rekomenduojame pradėti nuo paprastų formulių ir ryšių, kad pamatytumėte, kaip veikia kontekstas, tada pradėti eksperimentuoti su paprastomis formulėmis "PivotTable". Tolesniame skyriuje taip pat pateikiami keli pavyzdžiai, kaip formulės naudoja skirtingų tipų kontekstą dinamiškai pateikti rezultatus.
Formulių konteksto pavyzdžiai
-
Funkcija RELATED išplečia dabartinės eilutės kontekstą, kad įtrauktų reikšmes į susijusį stulpelį. Tai leidžia atlikti peržvalgas. Šioje temoje pateiktame pavyzdyje parodyta filtravimo sąveika ir eilučių kontekstas.
-
Funkcija FILTER leidžia nurodyti eilutes, kurias norite įtraukti į dabartinį kontekstą. Šios temos pavyzdžiuose taip pat parodyta, kaip įdėti filtrus į kitas funkcijas, kurios atlieka agreguotas.
-
Funkcija ALL formulėje nustato kontekstą. Jį galite naudoti norėdami perrašyti filtrus, kurie taikomi dėl užklausos konteksto.
-
Funkcija ALLEXCEPT leidžia pašalinti visus filtrus, išskyrus nurodytus. Abi temos apima pavyzdžius, kurie padės jums kurti formules ir suprasti sudėtingus kontekstus.
-
Funkcijos EARLIER ir EARLIEST leidžia atlikti lentelių ciklą atliekant skaičiavimus nurodant reikšmę iš vidinio ciklo. Jei esate susipažinę su rekursijos sąvoka ir su vidinių ir išorinių kilpų, jums bus įvertinti galios, kad ANKSČIAU ir EARLIEST funkcijos suteikia. Jei dar tik pradedate naudoti šias sąvokas, atidžiai atlikite pavyzdyje nurodytus veiksmus, kad pamatytumėte, kaip vidiniai ir išoriniai kontekstai naudojami skaičiuojant.
Nuorodų vientisumas
Šiame skyriuje aptariamos kai kurios išplėstinės sąvokos, susijusios su trūkstamomis reikšmėmis Power Pivot lentelėse, susietose ryšiais. Šis skyrius gali būti naudingas, jei turite darbaknyges su keliomis lentelėmis ir sudėtingomis formulėmis ir norite padėti suprasti rezultatus.
Jei dar tik pradedate naudoti sąryšinių duomenų sąvokas, rekomenduojame pirmiausia perskaityti įvadinę temą Ryšių apžvalga.
Nuorodų vientisumas ir Power Pivot ryšiai
Power Pivot nereikalaujama, kad būtų įgalintas nuorodų vientisumas tarp dviejų lentelių, kad būtų galima apibrėžti tinkamą ryšį. Vietoj to kiekvieno ryšio "vienas su daugeliu" pabaigoje sukuriama tuščia eilutė ir naudojama tvarkyti visas neatitinkančią eilutę iš susijusios lentelės. Ji efektyviai veikia kaip SQL išorinis sujungimas.
Jei "PivotTable" grupuojate duomenis pagal vieną ryšio pusę, visi nesuderinta informacija ryšio pusėje yra sugrupuoti ir bus įtraukti į sumas su tuščios eilutės antrašte. Tuščia antraštė apytikriai atitinka nežinomą narį.
Kas yra nežinomas narys
Nežinomo nario sąvoka jums greičiausiai pažįstama, jei dirbote su kelių dimensijų duomenų bazių sistemomis, pvz., SQL serverio analizės tarnybos. Jei terminas jums naujas, toliau pateiktame pavyzdyje paaiškinama, kas yra nežinomas narys ir kokią įtaką jis turi skaičiavimams.
Tarkime, kad kuriate skaičiavimą, kuris susumuoja kiekvienos parduotuvės mėnesio pardavimą, bet lentelės Pardavimas stulpelyje trūksta saugyklos pavadinimo reikšmės. Atsižvelgiant į tai, kad parduotuvės ir pardavimo lentelės yra sujungtos saugyklos pavadinimu, ko norėtumėte tikėtis formulėje? Kaip turi būti rodoma "PivotTable" grupė arba pardavimo skaičiai, kurie nėra susiję su esama saugykla?
Ši problema yra dažna duomenų sandėliuose, kur didelės faktų duomenų lentelės turi būti logiškai susijusios su dimensijų lentelėmis, kuriose yra informacijos apie saugyklas, regionus ir kitus atributus, naudojamus skirstyti į kategorijas ir apskaičiuoti faktus. Norint išspręsti šią problemą, visi nauji faktai, nesusiję su esamu objektu, laikinai priskiriami nežinomam nariui. Todėl nesusiję faktai bus rodomi sugrupuoti "PivotTable" po tuščia antrašte.
Tuščių reikšmių ir tuščios eilutės apdorojimas
Tuščios reikšmės skiriasi nuo tuščių eilučių, įtrauktų, kad tilptų nežinomas narys. Tuščia reikšmė yra speciali reikšmė, kuri naudojama nulinėms reikšmėms, tuščioms eilutėms ir kitoms trūkstamoms reikšmėms nurodyti. Daugiau informacijos apie tuščią reikšmę ir kitus DAX duomenų tipus žr. Duomenų modelių duomenų tipai.
