Šajā sadaļā sniegtas saites uz piemēriem, kuros demonstrēta DAX formulu izmantošana šādos scenārijos.
- Sarežģītu aprēķinu veikšana
- Darbs ar tekstu un datumiem
- Nosacījumvērtības un kļūdu pārbaude
- Laika informācijas izmantošana
- Vērtību vērtēšana un salīdzināšana
Tēmas šajā rakstā
Darba sākšana
Apmeklējiet DAX resursu centra vikivietni , kur varat atrast visu veidu informāciju par DAX, tostarp emuārus, paraugus, tehniskos dokumentus un video, ko nodrošina nozares vadošie profesionāļi un korporācija Microsoft.
Scenāriji: sarežģītu aprēķinu veikšana
DAX formulas var veikt sarežģītus aprēķinus, kas ietver pielāgotu apkopošanu, filtrēšanu un nosacījumvērtību izmantošanu. Šajā sadaļā sniegti piemēri, kā sākt darbu ar pielāgotiem aprēķiniem.
Pielāgotu aprēķinu izveide rakurstabulai
CALCULATE un CALCULATETABLE ir jaudīgas, elastīgas funkcijas, kas noder aprēķināto lauku definēšanai. Šīs funkcijas ļauj mainīt kontekstu, kurā tiks veikts aprēķins. Varat arī pielāgot apkopošanas vai matemātiskās operācijas tipu. Piemērus skatiet šajās tēmās.
Filtra lietošana formulai
Lielākajā daļā vietu, kur DAX funkcija tabulu izmanto kā argumentu, parasti var ievietot filtrētu tabulu, izmantojot funkciju FILTER, nevis tabulas nosaukumu, vai arī norādot filtra izteiksmi kā vienu no funkcijas argumentiem. Nākamajās tēmās ir sniegti piemēri par filtru izveidi un to, kā filtri ietekmē formulu rezultātus. Papildinformāciju skatiet sadaļā Datu filtrēšana DAX formulās.
Funkcija FILTER ļauj norādīt filtrēšanas kritērijus, izmantojot izteiksmi, savukārt citas funkcijas ir īpaši izstrādātas tukšu vērtību filtrēšanai.
Filtru noņemšana selektīvi, lai izveidotu dinamisku attiecību
Formulās izveidojot dinamiskos filtrus, varat viegli atbildēt uz šādiem jautājumiem:
- Kāda bija pašreizējā produkta pārdošanas daļa gada kopējos pārdošanas datos?
- Cik lielā mērā šī nodaļa ir devusi ieguldījumu kopējā peļņā visos darbības gados, salīdzinot ar citām nodaļām?
Rakurstabulā izmantojamās formulas var ietekmēt rakurstabulas konteksts, taču varat selektīvi mainīt kontekstu, pievienojot vai noņemot filtrus. Piemērā tēmā VISI ir parādīts, kā to paveikt. Lai atrastu konkrēta tālākpārdevēja pārdošanas attiecību pret visu tālākpārdevēju pārdošanas datiem, izveidojiet rādītāju, kas pašreizējā konteksta vērtību aprēķina ar konteksta ALL vērtību.
Tēmā ALLEXCEPT ir sniegts piemērs tam, kā selektīvi notīrīt filtrus formulā. Abos piemēros izskaidrots, kā mainās rezultāti atkarībā no rakurstabulas noformējuma.
Citus attiecību un procentuālo vērtību aprēķināšanas piemērus skatiet šajās tēmās:
Vērtības izmantošana no ārējās cilpas
Papildus pašreizējā konteksta vērtību izmantošanai aprēķinos, DAX var izmantot vērtību no iepriekšējās cilpas, izveidojot saistītu aprēķinu kopu. Šajā tēmā ir sniegts detalizēts apraksts, kā izveidot formulu, kas atsaucas uz vērtību no ārējās cilpas. Funkcija EARLIER atbalsta ne vairāk kā divus ligzdoto cilpu līmeņus.
Lai uzzinātu vairāk par rindas kontekstu un saistītajām tabulām, kā arī par to, kā formulās izmantot šo jēdzienu, skatiet rakstu Konteksts DAX formulās.
Scenāriji: darbs ar tekstu un datumiem
Šajā sadaļā sniegtas saites uz DAX atsauces tēmām, kurās ir raksturīgi piemēri darbam ar tekstu, datuma un laika vērtību izgūšanu un komponēšanu vai vērtību izveidi, pamatojoties uz nosacījumu.
Atslēgas kolonnas izveide, izmantojot konkatenāciju
Power Pivot neatļauj saliktās atslēgas; Tāpēc, ja jūsu datu avotā ir kompozītatslēgas, tās var būt jāapvieno vienā atslēgas kolonnā. Nākamajā tēmā ir sniegts piemērs tam, kā izveidot aprēķināto kolonnu, pamatojoties uz kompozītatslēgu.
Compose Date based of date parts extracted from a text date date
Darbam ar datumiem Power Pivot izmanto SQL Server datuma/laika datu tipu; tāpēc, ja jūsu ārējie dati satur datumus, kas ir formatēti citādi, piemēram, ja datumi ir rakstīti reģionālajā datumu formātā, ko neatpazīst Power Pivot datu programma, vai ja jūsu dati izmanto veselu skaitļu surogātatslēgas, iespējams, būs jāizmanto DAX formula, lai izvilktu datuma daļas un pēc tam sastādītu daļas derīgā datumā/ laika attēlojums.
Piemēram, ja jums ir kolonnu ar datumiem, kas attēloti kā veseli skaitļi un pēc tam importēti kā teksta virkne, varat pārvērst virkni par datuma/laika vērtību, izmantojot šādu formulu:
=DATE(RIGHT([Vērtība1],4),LEFT([Vērtība1],2),MID([Vērtība1],2))
| Vērtība1 | Rezultāts |
|---|---|
| 01032009 | 1/3/2009 |
| 12132008 | 12/13/2008 |
| 06252007 | 6/25/2007 |
Tālāk norādītajās tēmās ir sniegta papildinformācija par funkcijām, kas tiek izmantotas datumu izgūšanai un sastādīšanai.
Pielāgota datuma vai skaitļu formāta definēšana
Ja datos ir datumi vai skaitļi, kas netiek attēloti nevienā no standarta Windows teksta formātiem, varat definēt pielāgotu formātu, lai nodrošinātu, ka vērtības tiek apstrādātas pareizi. Šos formātus izmanto, konvertējot vērtības par virknēm vai no virknēm. Tālāk norādītajās tēmās ir sniegts detalizēts saraksts ar iepriekš definētiem formātiem, kas ir pieejami darbam ar datumiem un skaitļiem.
- Iepriekš definēti skaitliskie formāti funkcijai FORMAT
- Pielāgoti skaitliskie formāti funkcijai FORMAT
- Iepriekš definētie datuma un laika formāti funkcijā FORMAT
- Pielāgoti datuma un laika formāti funkcijā FORMAT
Datu tipu maiņa, izmantojot formulu
Pievienojumprogrammā Power Pivot izvades datu tipu nosaka avota kolonnas, un jūs nevarat skaidri norādīt rezultāta datu tipu, jo optimālo datu tipu nosaka Power Pivot. Tomēr varat izmantot netiešo datu tipu pārveidošanu, ko veic Power Pivot, lai manipulētu ar izvades datu tipu.
- Lai datumu vai skaitļu virkni pārvērstu par skaitli, reiziniet ar 1,0. Piemēram, šī formula aprēķina pašreizējo datumu mīnus 3 dienas un pēc tam izvada atbilstošo veselo skaitli.
=(TODAY()-3)*1.0 - Lai konvertētu datumu, skaitli vai valūtas vērtību uz virkni, savienojiet vērtību ar tukšu virkni. Piemēram, šī formula atgriež šodienas datumu kā virkni.
=""& TODAY()
Lai nodrošinātu konkrēta datu tipa atgriešanu, var izmantot arī šādas funkcijas:
Reālu skaitļu pārvēršana par veseliem skaitļiem
- Funkcija ROUND
- Funkcija CEILING
-
Funkcija FLOOR
Reālu skaitļu, veselu skaitļu vai datumu konvertēšana virknēs - Funkcija FIXED
-
Funkcija FORMAT
Virkņu konvertēšana par reāliem skaitļiem vai datumiem - Funkcija VALUE
- Funkcija DATEVALUE
- Funkcija TIMEVALUE
Scenārijs: nosacītas vērtības un kļūdu pārbaude
Tāpat kā programmā Excel, arī DAX ir funkcijas, kas ļauj pārbaudīt datu vērtības un atgriezt atšķirīgu vērtību, pamatojoties uz nosacījumu. Piemēram, varat izveidot aprēķinātu kolonnu, kurā tālākpārdevēji tiek atzīmēti kā Vēlamais vai Vērtība atkarībā no gada pārdošanas apjoma. Funkcijas, kas pārbauda vērtības, ir noderīgas arī vērtību diapazona vai tipa pārbaudei, lai novērstu neparedzētu datu kļūdu kļūdu kļūdu aprēķinu darbībā.
Vērtības izveide, pamatojoties uz nosacījumu
Varat izmantot ligzdotus IF nosacījumus, lai pārbaudītu vērtības un nosacījumveidā ģenerētu jaunas vērtības. Šajās tēmās ir sniegti daži vienkārši nosacījumapstrādes un nosacījumvērtību piemēri:
Kļūdu pārbaude formulā
Atšķirībā no programmas Excel vienā aprēķinātās kolonnas rindā nevar būt derīgas vērtības, bet nederīgas — otrā rindā. Respektīvi, ja kādā Power Pivot kolonnas daļā ir kļūda, visa kolonna tiek atzīmēta ar kļūdu, lai jūs vienmēr izlabotu formulu kļūdas, kas rada nederīgas vērtības.
Piemēram, ja izveidojat formulu, kas dala ar nulli, var tikt parādīts bezgalības rezultāts vai kļūda. Dažas formulas arī neizdosies, ja funkcija uzrāda tukšu vērtību, kad tā sagaida skaitlisku vērtību. Datu modeļa izstrādes laikā vislabāk ļaut kļūdām parādīties, lai jūs varētu noklikšķināt uz ziņojuma un novērst problēmu. Tomēr, publicējot darbgrāmatas, ieteicams iekļaut kļūdu apstrādi, lai novērstu neparedzētu vērtību izraisītu aprēķinu kļūmi.
Lai izvairītos no kļūdu atgriešanas aprēķinātajā kolonnā, izmantojiet loģisko un informācijas funkciju kombināciju, lai pārbaudītu, vai nav kļūdu un vienmēr atgrieztas derīgas vērtības. Tālāk norādītajās tēmās sniegti daži vienkārši piemēri tam, kā to izdarīt DAX:
Scenāriji: laika informācijas izmantošana
DAX laika informācijas funkcijas ietver funkcijas, kas palīdz no datiem izgūt datumus vai datumu diapazonus. Pēc tam varat izmantot šos datumus vai datumu diapazonus, lai aprēķinātu vērtības līdzīgos periodos. Laika informācijas funkcijas ietver arī funkcijas, kas darbojas ar standarta datumu intervāliem, lai ļautu salīdzināt vērtību starp mēnešiem, gadiem vai ceturkšņiem. Varat arī izveidot formulu, kas salīdzina noteikta perioda pirmā un pēdējā datuma vērtības.
Visu laika informācijas funkciju sarakstu skatiet sadaļā Laika informācijas funkcijas (DAX). Padomus par datumu un laiku efektīvu izmantošanu Power Pivot analīzē skatiet sadaļā Datumi pievienojumprogrammā Power Pivot.
Aprēķināt kumulatīvo pārdošanas apjomu
Nākamajās tēmās ir sniegti piemēri par to, kā aprēķināt slēgšanas un sākuma bilances. Piemēros varat izveidot tekošos atlikumus dažādiem intervāliem, piemēram, dienām, mēnešiem, ceturkšņiem vai gadiem.
- Funkcija CLOSINGBALANCEMONTH, funkcija CLOSINGBALANCEQUARTER, funkcija CLOSINGBALANCEYEAR
- Funkcija OPENINGBALANCEMONTH, funkcija OPENINGBALANCEQUARTER, funkcija OPENINGBALANCEYEAR
Vērtību salīdzināšana laika gaitā
Nākamajās tēmās ir sniegti piemēri par to, kā salīdzināt summas dažādos laika periodos. Noklusējuma laika periodi, ko atbalsta DAX, ir mēneši, ceturkšņi un gadi.
- Funkcija PREVIOUSMONTH, funkcija PREVIOUSQUARTER, PREVIOUSYEAR
- Funkcija TOTALMTD, funkcija TOTALQTD, funkcija TOTALYTD
- Funkcija PARALLELPERIOD
Vērtības aprēķināšana pielāgotā datumu diapazonā
Skatiet tālāk norādītās tēmas, lai uzzinātu, kā izgūt piemērus par to, kā izgūt pielāgotus datumu diapazonus, piemēram, pirmās 15 dienas pēc pārdošanas veicināšanas sākuma.
Ja izmantojat laika informācijas funkcijas, lai izgūtu pielāgotu datumu kopu, varat izmantot šo datumu kopu kā ievadi funkcijā, kas veic aprēķinus, lai izveidotu pielāgotus apkopojumus starp laika periodiem. Piemēru tam, kā to paveikt, skatiet nākamajā tēmā:
-
Piezīme
Ja nav jānorāda pielāgots datumu diapazons, bet strādājat ar standarta uzskaites vienībām, piemēram, mēnešiem, ceturkšņiem vai gadiem, aprēķinus ieteicams veikt, izmantojot šim nolūkam paredzētās laika informācijas funkcijas, piemēram, TOTALQTD, TOTALMTD, TOTALQTD u.c.
Scenāriji: vērtību vērtēšana un salīdzināšana
Lai rādītu tikai lielāko n vienumu skaitu kolonnā vai rakurstabulā, ir vairākas opcijas:
- Varat izmantot programmas Excel līdzekļus, lai izveidotu augšējo filtru. Varat arī atlasīt vairākas lielākās vai mazākās vērtības rakurstabulā. Pirmajā šīs sadaļas daļā ir aprakstīts, kā filtrēt 10 pirmos vienumus rakurstabulā. Papildinformāciju skatiet Excel dokumentācijā.
- Varat izveidot formulu, kas dinamiski nosaka vērtību rangu, un pēc tam filtrēt pēc vērtēšanas vērtībām vai izmantot vērtēšanas vērtību kā datu griezumu. Otrajā šīs sadaļas daļā ir aprakstīts, kā izveidot šo formulu un pēc tam izmantot šo rangu datu griezumā.
Katrai metodei ir priekšrocības un trūkumi.
- Excel augšējo filtru ir viegli izmantot, bet tas ir paredzēts tikai attēlošanai. Ja rakurstabulas pamatā esošie dati mainās, rakurstabula ir jāatsvaidzina manuāli, lai redzētu izmaiņas. Ja nepieciešams dinamiski strādāt ar vērtējumiem, varat izmantot DAX, lai izveidotu formulu, kas salīdzina vērtības ar citām vērtībām kolonnā.
- DAX formula ir jaudīgāka; Turklāt, pievienojot vērtēšanas vērtību datu griezumam, varat vienkārši noklikšķināt uz datu griezuma, lai mainītu parādīto galveno vērtību skaitu. Tomēr aprēķini ir skaitļošanas ziņā dārgi, un šī metode var nebūt piemērota tabulām ar daudzām rindām.
Tikai desmit pirmo vienumu rādīšana rakurstabulā
Lielāko vai mazāko vērtību rādīšana rakurstabulā
|
|---|
Vienumu dinamiska kārtošana, izmantojot formulu
Šajā tēmā ir sniegts piemērs par to, kā izmantot DAX, lai izveidotu aprēķināto kolonnā saglabātu rangu. Tā kā DAX formulas tiek aprēķinātas dinamiski, vienmēr varat būt pārliecināts, ka vērtēšana ir pareiza, pat ja pamatā esošie dati ir mainījušies. Turklāt, tā kā formula tiek izmantota aprēķinātā kolonnā, varat izmantot rangu datu griezumā un pēc tam atlasīt 5, 10 vai pat 100 lielākās vērtības.