Konteksts ļauj veikt dinamisku analīzi, kurā formulas rezultāti var mainīties, lai atspoguļotu pašreizējo rindas vai šūnas atlasi, kā arī visus saistītos datus. Konteksta izpratne un efektīva konteksta izmantošana ir ļoti svarīga, lai veidotu augstas veiktspējas formulas, dinamisko analīzi un novērstu problēmas formulās.
Šajā sadaļā ir definēti dažādi konteksta veidi: rindas konteksts, vaicājuma konteksts un filtra konteksts. Tajā ir izskaidrots, kā tiek novērtēts konteksts formulām aprēķinātajās kolonnās un rakurstabulās.
Šī raksta pēdējā daļā sniegtas saites uz detalizētiem piemēriem, kas parāda, kā formulu rezultāti mainās atkarībā no konteksta.
Izpratne par kontekstu
Power Pivot formulas var ietekmēt rakurstabulā lietotie filtri, tabulu relācijas un formulās izmantotie filtri. Konteksts ir tas, kas ļauj veikt dinamisko analīzi. Konteksta izpratne ir svarīga, veidojot formulas un novēršot to problēmas.
Ir dažādi konteksta veidi: rindas konteksts, vaicājuma konteksts un filtra konteksts.
Rindas kontekstu var uzskatīt par "pašreizējo rindu". Ja esat izveidojis aprēķināto kolonnu, rindas konteksts sastāv no vērtībām katrā atsevišķā rindā un vērtībām kolonnās, kas ir saistītas ar pašreizējo rindu. Ir arī dažas funkcijas (EARLIER un EARLYT), kas iegūst vērtību no pašreizējās rindas un pēc tam izmanto šo vērtību, veicot darbību visā tabulā.
Vaicājuma konteksts attiecas uz datu apakškopu, kas netieši tiek izveidota katrai rakurstabulas šūnai atkarībā no rindu un kolonnu galvenēm.
Filtra konteksts ir katrā kolonnā atļauto vērtību kopa, pamatojoties uz rindai lietotajiem filtra ierobežojumiem vai kurus definē filtra izteiksmes formulā.
Rindas konteksts
Ja formulu veidojat aprēķinātā kolonnā, šīs formulas rindas kontekstā ir iekļautas vērtības no visām kolonnām pašreizējā rindā. Ja tabula ir saistīta ar citu tabulu, saturā ir iekļautas arī visas vērtības no šīs citas tabulas, kas ir saistītas ar pašreizējo rindu.
Piemēram, jūs izveidojat aprēķinātu kolonnu =[Transports] + [Nodoklis], kurā saskaita divas kolonnas no vienas tabulas. Šī formula darbojas kā formulas Excel tabulā, kas automātiski atsaucas uz vērtībām no vienas rindas. Ņemiet vērā, ka tabulas atšķiras no diapazoniem: izmantojot diapazonu apzīmēšanu, nevar sniegt atsauci uz vērtību no rindas pirms pašreizējās rindas, kā arī nevar sniegt atsauci uz jebkuru patvaļīgu atsevišķu vērtību tabulā vai šūnā. Vienmēr ir jāstrādā ar tabulām un kolonnām.
Rindas konteksts automātiski seko relācijām starp tabulām, lai noteiktu, kuras saistīto tabulu rindas ir saistītas ar pašreizējo rindu.
Piemēram, šajā formulā tiek izmantota funkcija RELATED, lai iznestu nodokļu vērtību no saistītas tabulas, pamatojoties uz reģionu, uz kuru tika nosūtīts pasūtījums. Nodokļa vērtību nosaka, izmantojot reģiona vērtību pašreizējā tabulā, meklējot reģionu saistītajā tabulā un pēc tam iegūstot nodokļa likmi šim reģionam no saistītās tabulas.
= [Transports] + RELATED('Reģions'[Nodokļu likme])
Šī formula vienkārši iegūst nodokļa likmi pašreizējam reģionam no tabulas Region. Jums nav jāzina vai jānorāda atslēga, kas savieno tabulas.
Vairāku rindu konteksts
Turklāt DAX ietver funkcijas, kas atkārto aprēķinus tabulā. Šīm funkcijām var būt vairākas pašreizējās rindas un pašreizējo rindu konteksti. Programmēšanas terminos jūs varat izveidot formulas, kas atkārtojas iekšējā un ārējā cilpā.
Piemēram, jūsu darbgrāmatā ir tabulas Produkti un tabulas Pārdošana . Iespējams, vēlēsities pārskatīt visu pārdošanas tabulu, kas ir pilna ar transakcijām ar vairākiem produktiem, un vienā transakcijā atrast lielāko pasūtīto daudzumu katram produktam.
Programmā Excel šim aprēķinam ir nepieciešama starpposma kopsavilkumu sērija, kas datu izmaiņu gadījumā būtu jāpārveido. Ja esat pieredzējis programmas Excel lietotājs, iespējams, varēsit izveidot masīva formulas, kas paveiktu šo uzdevumu. Vai arī relāciju datu bāzē varat rakstīt ligzdotus apakšatlases vienumus.
Tomēr ar DAX varat izveidot vienu formulu, kas atgriež pareizo vērtību, un rezultāti tiek automātiski atjaunināti ikreiz, kad tabulām pievienojat datus.
=MAXX(FILTER(Sales,[ProdKey]=BEFORE([ProdKey])),Sales[OrderQty])
Detalizētu informāciju par šo formulu skatiet funkcijā BEFORE.
Īsi sakot, funkcija EARLIER saglabā rindas kontekstu no darbības, kas tika veikta pirms pašreizējās operācijas. Funkcija vienmēr saglabā atmiņā divas konteksta kopas: viena konteksta kopa norāda pašreizējo rindu formulas iekšējai cilpai, un otra konteksta kopa norāda pašreizējo rindu formulas ārējai cilpai. DAX automātiski padod vērtības starp abām cilpām, lai varat izveidot sarežģītus apkopojumus.
Vaicājuma konteksts
Vaicājuma konteksts ir datu apakškopa, kas ir netieši izgūta formulai. Kad nometat mēru vai citu vērtību lauku rakurstabulas šūnā, Power Pivot programma pārbauda rindu un kolonnu galvenes, datu griezumus un atskaišu filtrus, lai noteiktu kontekstu. Pēc tam Power Pivot veic nepieciešamos aprēķinus, lai aizpildītu katru rakurstabulas šūnu. Izgūto datu kopa ir katras šūnas vaicājuma konteksts.
Tā kā konteksts var mainīties atkarībā no formulas atrašanās vietas, formulas rezultāti mainās arī atkarībā no tā, vai izmantojat formulu rakurstabulā ar daudziem grupējumiem un filtriem, vai aprēķinātā kolonnā bez filtriem un minimāla konteksta.
Piemēram, pieņemsim, ka izveidojat šo vienkāršo formulu, kas summē tabulas Pārdošana kolonnas Peļņa vērtības:
=SUM('Pārdošana'[Peļņa])
Ja izmantojat šo formulu aprēķinātajā kolonnā tabulā Pārdošana , formulas rezultāti būs vienādi visā tabulā, jo formulas vaicājuma konteksts vienmēr ir visa tabulas Pārdošana datu kopa. Jūsu rezultātiem būs peļņa visiem reģioniem, visiem produktiem, visiem gadiem utt.
Tomēr parasti jūs nevēlaties vienu un to pašu rezultātu skatīt simtiem reižu, bet gan iegūt peļņu par noteiktu gadu, konkrētu valsti vai reģionu, konkrētu produktu vai kādu šo vienumu kombināciju un pēc tam iegūt gala summu.
Rakurstabulā ir viegli mainīt kontekstu, pievienojot vai noņemot kolonnu un rindu galvenes un pievienojot vai noņemot datu griezumus. Varat izveidot formulu, piemēram, iepriekš norādīto, kā mēru un pēc tam to nomest rakurstabulā. Pievienojot kolonnu vai rindu virsrakstus rakurstabulai, tiek mainīts vaicājuma konteksts, kurā tiek novērtēts mērījums. Sadalīšanas un filtrēšanas darbības ietekmē arī kontekstu. Tāpēc viena un tā pati formula, kas tiek izmantota rakurstabulā, katrai šūnai tiek novērtēta atšķirīgā vaicājuma kontekstā.
Filtra konteksts
Filtra konteksts tiek pievienots, kad norādāt filtra ierobežojumus kolonnā vai tabulā atļautajai vērtību kopai, izmantojot formulas argumentus. Filtra konteksts tiek lietots virs citiem kontekstiem, piemēram, rindas konteksta vai vaicājuma konteksta.
Piemēram, rakurstabula aprēķina vērtības katrai šūnai, pamatojoties uz rindu un kolonnu virsrakstiem, kā aprakstīts iepriekšējā sadaļā par vaicājumu kontekstu. Tomēr mēros vai aprēķinātajās kolonnās, ko pievienojat rakurstabulai, varat norādīt filtru izteiksmes, lai kontrolētu formulas izmantotās vērtības. Varat arī selektīvi notīrīt filtrus konkrētās kolonnās.
Papildinformāciju par filtru izveidi formulās skatiet sadaļā Filtra funkcijas.
Piemēru par filtru notīrīšanu, lai veidotu gala summas, skatiet sadaļā Funkcija ALL.
Piemērus par to, kā formulās selektīvi notīrīt un lietot filtrus, skatiet sadaļā Funkcija ALLCOMPAT.
Tādēļ ir jāpārskata rakurstabulā izmantoto mēru vai formulu definīcijas, lai, interpretējot formulu rezultātus, tiktu ņemts vērā filtra konteksts.
Konteksta noteikšana formulās
Kad veidojat formulu, Power Pivot programmai Excel vispirms pārbauda vispārīgo sintaksi, pēc tam pārbauda jūsu nodrošināto kolonnu un tabulu nosaukumus, salīdzinot ar iespējamajām kolonnām un tabulām pašreizējā kontekstā. Ja Power Pivot nevar atrast formulas norādītās kolonnas un tabulas, parādās kļūdas ziņojums.
Konteksts tiek noteikts, kā aprakstīts iepriekšējās sadaļās, izmantojot darbgrāmatā pieejamās tabulas, tabulu relācijas un lietotos filtrus.
Piemēram, ja tikko importējāt datus jaunā tabulā un neesat lietojis nevienu filtru, visa tabulas kolonnu kopa ir daļa no pašreizējā konteksta. Ja jums ir vairākas tabulas, kuras ir saistītas ar relācijām, un jūs strādājat rakurstabulā, kas ir filtrēta, pievienojot kolonnu virsrakstus un izmantojot datu griezumus, konteksts ietver saistītās tabulas un visus datu filtrus.
Konteksts ir ļoti svarīgs jēdziens, kas var arī apgrūtināt problēmu novēršanu ar formulām. Iesakām sākt ar vienkāršām formulām un relācijām, lai noskaidrotu, kā darbojas konteksts, un pēc tam sākt eksperimentēt ar vienkāršām formulām rakurstabulās. Nākamajā sadaļā sniegti arī daži piemēri tam, kā formulas izmanto dažāda veida kontekstu, lai dinamiski atgrieztu rezultātus.
Konteksta piemēri formulās
- Funkcija RELATED izvērš pašreizējās rindas kontekstu, lai iekļautu vērtības saistītā kolonnā. Tas ļauj veikt uzmeklēšanu. Piemērs šajā tēmā ilustrē filtrēšanas un rindas konteksta mijiedarbību.
- Funkcija FILTER ļauj norādīt rindas, kas jāiekļauj pašreizējā kontekstā. Šajā tēmā iekļautie piemēri parāda arī to, kā iegult filtrus citās funkcijās, kas veic apkopošanu.
- Funkcija ALL iestata kontekstu formulā. Varat to izmantot, lai ignorētu filtrus, kas tiek lietoti kā vaicājuma konteksta rezultāts.
- Funkcija ALLEXCEPT ļauj noņemt visus filtrus, izņemot vienu, ko norādāt. Abās tēmās iekļauti piemēri, kas palīdz izveidot formulas un izprast sarežģītu kontekstu.
- Funkcijas EARLIER un EARLIEST ļauj pārvietoties tabulās, veicot aprēķinus, vienlaikus veidojot atsauci uz vērtību no iekšējās cilpas. Ja pārzināt rekursijas jēdzienu un iekšējās un ārējās cilpas, jūs novērtēsiet jaudu, ko sniedz funkcija EARLIER un EARLY. Ja esat iesācējs darbā ar šiem jēdzieniem, jums rūpīgi jāizpilda piemērā norādītās darbības, lai redzētu, kā aprēķinos tiek izmantots iekšējais un ārējais konteksts.
Attiecinošā integritāte
Šajā sadaļā ir aplūkoti daži papildu jēdzieni, kas saistīti ar trūkstošām vērtībām Power Pivot tabulās, kuras ir savienotas ar relācijām. Šī sadaļa var noderēt, ja jums ir darbgrāmatas ar vairākām tabulām un sarežģītām formulām un vēlaties palīdzību rezultātu izprašanai.
Ja esat iesācējs darbā ar relāciju datu koncepcijām, ieteicams vispirms izlasīt ievadtēmu "Relāciju pārskats".
Attiecinošā integritāte un Power Pivot relācijas
Power Pivot nepieprasa, lai starp divām tabulām tiktu ieviesta attiecinošā integritāte, lai definētu derīgu relāciju. Tā vietā katras relācijas viens pret daudziem galā "viens" tiek izveidota tukša rinda, kas tiek izmantota, lai apstrādātu visas neatbilstošās rindas no saistītās tabulas. Tas efektīvi darbojas kā SQL ārējais savienojums.
Ja rakurstabulās datus grupējat pēc relācijas vienas puses, visi neatbilstošie dati relācijas daudzo pusē tiek sagrupēti kopā un tiek iekļauti kopsummās ar tukšu rindas virsrakstu. Tukšais virsraksts ir aptuveni vienāds ar "nezināms dalībnieks".
Izpratne par nezināmo dalībnieku
Nezināmā dalībnieka jēdziens, iespējams, jums ir pazīstams, ja esat strādājis ar daudzdimensiju datu bāzu sistēmām, piemēram, SQL Server analītiskie pakalpojumi (SSAS). Ja šis termins jums ir jauns, tālāk sniegtajā piemērā tiek paskaidrots, kas ir nezināmais elements un kā tas ietekmē aprēķinus.
Pieņemsim, ka veicat aprēķinu, kas summē mēneša pārdošanas apjomus katram veikalam, bet tabulas Pārdošana kolonnā trūkst veikala nosaukuma vērtības. Ņemot vērā, ka tabulas Veikals un Pārdošana ir saistītas ar veikala nosaukumu, kas varētu notikt formulā? Kā rakurstabulā jāgrupē vai jāparāda pārdošanas rādītāji, kas nav saistīti ar esošu veikalu?
Šī problēma ir bieži sastopama datu noliktavās, kur lielām faktu datu tabulām jābūt loģiski saistītām ar dimensiju tabulām, kurās ir informācija par veikaliem, reģioniem un citiem atribūtiem, kas tiek izmantoti faktu kategorizēšanai un aprēķināšanai. Lai novērstu problēmu, visi jaunie fakti, kas nav saistīti ar esošu entītiju, tiek īslaicīgi piešķirti nezināmajam dalībniekam. Tāpēc nesaistītie fakti rakurstabulā tiks rādīti grupēti zem tukša virsraksta.
Tukšu vērtību salīdzināšana ar tukšo rindu
Tukšās vērtības atšķiras no tukšajām rindām, kas tiek pievienotas, lai pielāgotu nezināmo elementu. Tukšā vērtība ir īpaša vērtība, kas tiek izmantota, lai norādītu vērtības Null, tukšas virknes un citas trūkstošās vērtības. Papildinformāciju par tukšo vērtību, kā arī citiem DAX datu tipiem skatiet sadaļā Datu tipi datu modeļos.