Amamodeli e-DAX ku-Power Pivot: amafomula, umongo, kanye nemikhuba emihle kakhulu

Uma usebenza ngedatha ku-Excel kanye I-Pivot yamandlaNgokushesha noma kamuva uzohlangana ne-DAX. Nakuba igama elithi “Izinkulumo Zokuhlaziywa Kwedatha” lizwakala liyizobuchwepheshe futhi liyesabisa kancaneIqiniso liwukuthi, ngokuqonda kahle izisekelo, kuba ithuluzi elilula kakhulu nelinamandla lokwenza amamodeli olwazi.

Ku-Power Pivot, i-DAX iyinhliziyo yamamodeli ethebula: Isetshenziselwa ukudala amakholomu nezilinganiso ezibaliwe ezondla amathebula namashadi e-pivotIkuvumela ukuthi udlale ngomongo wezihlungi, usebenze ngezinsuku, uphathe amaphutha, futhi uqhathanise ku el tiempo Nokunye okuningi. Ake sibheke kabanzi ukuthi konke lokhu kuhambisana kanjani namamodeli e-DAX ku-Power Pivot nokuthi singawasebenzisa kanjani ngaphandle kokuhlanya.

Iyini i-DAX futhi kungani ibalulekile kumamodeli e-Power Pivot?

I-DAX (Izincazelo Zokuhlaziywa Kwedatha) ngaphezu kwakho konke, ulimi lwefomula oluklanyelwe amamodeli edathaakulona ulimi lwe- uhlelo Okuvamile. Umsebenzi wayo ukuchaza izibalo ezenziwe ngokwezifiso ezisetshenziswa kumathebula nasemakholomu kumodeli yakho ye-Power Pivot.

Nakuba kungabonakala sengathi Excel amafomula, I-DAX yakhelwe ukusebenza ngedatha yobudlelwano kanye nokuhlanganiswa okuguquguqukayo.Lokhu kusho ukuthi ihlangana kahle namathebula axhunywe ngobudlelwano, amathebula e-pivot, namamodeli ayinkimbinkimbi lapho umongo wesihlungi ushintsha khona kuye ngezinkambu ozisebenzisayo emigqeni, kumakholomu, noma kuma-slicer.

Ngaphakathi kwemodeli ye-Power Pivot, ungasebenzisa i-DAX ukuze Izinhlobo ezimbili eziyinhloko zezinto: amakholomu abaliwe kanye nezilinganisoZombili zihlala ndawonye ngaphakathi kwemodeli efanayo, kodwa ngayinye inenhloso ehlukile futhi ihlolwa ngendlela ehlukile, into okufanele icace kakhulu ukuze ingahlanganisi imiqondo.

Ngaphezu kwalokho, i-DAX ihlanganisa umbhalo, usuku nesikhathi, imisebenzi enengqondo, yezibalo, yokuhlunga kanye "nobuhlakani besikhathi"kanye nemisebenzi ebuyisela amathebula aphelele ngenxa yalokho. Le misebenzi yokugcina ingenye yomehluko omkhulu uma iqhathaniswa ne-Excel, lapho amafomula engabuyisi amathebula njengoba enjalo, kodwa kuma-array amaningi ngaphakathi kwebanga lamaseli.

Ukubuka konke kwamafomula e-DAX kanye nebha yefomula

Amafomula e-DAX alandela isakhiwo esifana kakhulu nese-Excel: Ziqala ngophawu olulinganayo (=) bese kubhalwa inkulumo noma umsebenzi onezimpikiswano zawoUngahlanganisa ama-opharetha, imisebenzi, ikholomu kanye nezinkomba zethebula, njll., njengoba nje ubungenza lapho wakha ifomula eyinkimbinkimbi kuspredishithi.

Nokho, kunama-nuances abalulekile. I-DAX ayisebenzi neze nezinkomba zohlobo lwe-A1:C10Kunalokho, kubhekiselwa kumakholomu noma amatafula aphelele, isibonelo 'Ukuthengisa' o 'Ikhalenda'Lokhu kunengqondo kakhulu kumodeli yethebula, kodwa kukuphoqa ukuthi ushintshe indlela ocabanga ngayo uma uvela ezweni le-Excel "eliyiseli ngeseli".

I-Power Pivot ine ibha yefomula efana neyama-Excel okwenza kube lula kakhulu ukudalwa kwezinkulumo. Ifaka i-Autocomplete yemisebenzi, amathebula, namakholomu: njengoba uqala ukuthayipha igama lethebula noma ikholomu, kuvela uhlu oluya phansi olunezinketho ezivumelekile, okunciphisa amaphutha e-syntax futhi konga isikhathi.

Ukuze ubhale amagama ethebula, vele uqale ukuthayipha bese ulivumela lisebenze. Ukuqedela ngokuzenzakalela kusikisela amagama afanayoKumakholomu, ungavula i-bracket bese ukhetha ikholomu kuthebula lamanje, noma uthayiphe igama lethebula kulandelwe ama-bracket bese ukhetha ohlwini.

Nokho, ngokungafani ne-Excel, I-Power Pivot ayivali ngokuzenzakalelayo ama-parentheses Akufanelani nawe. Kungumthwalo wakho ukuqinisekisa ukuthi imisebenzi yakheke kahle, inenani elifanele lama-agumenti kanye nama-parentheses aphelele, ngaphandle kwalokho ifomula ayikwazi ukulondolozwa noma ukusetshenziswa.

Lapho kusetshenziswa khona amafomula e-DAX: amakholomu nezilinganiso ezibaliwe

Kumodeli ye-Power Pivot ungabhala amafomula e-DAX ku- amakholomu abaliwe futhi ku izinyathelo (okubizwa nangokuthi amasimu abaliwe kumongo wamathebula e-pivot). Nakuba abelana ngolimi olufanayo, aziphatha ngezindlela ezihluke kakhulu.

Amakholomu abaliwe ku-Power Pivot

Ikholomu ebaliwe yi insimu entsha oyifaka etafuleni elikhona kumodeliEsikhundleni sokungenisa lelo nani kusuka emthonjeni wedatha, uchaza ifomula ye-DAX ehlolwa umugqa ngomugqa. Umphumela ugcinwa kukholomu yomugqa ngamunye etafuleni.

Amakholomu abaliwe asetshenziswa ngokufanayo kuyo yonke imigqa: Awukwazi ukuba nefomula ehlukile yomugqa ngamunye.Ngokungafani ne-Excel, lapho ungahudula khona bese uphonsa izingxenye ngesandla, ku-Power Pivot, isisho osichazayo sihlolwa ngokuzenzakalelayo kulo lonke ikholomu bese sibalwa kabusha lapho idatha ibuyekezwa noma kubalwa kabusha imodeli ngenkani.

Lolu hlobo lwekholomu lungasekelwa ku- amanye amakholomu abaliwe noma ngezilinganisoKodwa-ke, kunconywa ukuthi ungasebenzisi igama elifanayo kokubili isilinganiso kanye nekholomu ukuze ugweme ukudideka lapho ubhekisela. Kungumqondo omuhle kakhulu ukusebenzisa njalo ikholomu ephelele (Ithebula) ukuze ugweme ukubhekisela ngephutha esilinganisweni esinegama elifanayo.

Amakholomu abaliwe afaneleka kakhulu uma udinga izimfanelo ezengeziwe ofuna ukuzisebenzisa emigqeni, kumakholomu, ezihlungini, noma ezisikeni yamathebula e-pivot, noma njengezihluthulelo zobudlelwano. Cabanga, isibonelo, ngekholomu ethi "Margin" ebalwa njenge - ongayisebenzisa ukuqoqa noma ukuhlunga.

Izilinganiso noma amasimu abaliwe

Izinyathelo, ngokwazo, ziyi izibalo ezihlolwa kumongo wethebula le-pivot noma umbonoAzigcinwa ngomugqa, kodwa zibalwa kabusha ngokushesha ngenhlanganisela ngayinye yezihlungi, imigqa namakholomu asebenzayo embikweni.

Una ukulinganisa okuvamile Kungaba yinto elula njengokuthi:

Isamba Sokuthengisa = I-SUM(Ukuthengisa)

Lesi silinganiso, sibekwe endaweni ye Amanani Kuthebula le-pivot, iseli ngalinye lihlolwa ngokuya ngomongo (isibonelo, ngonyaka, ngomkhiqizo, ngesifunda...). Ukubala okufanayo kubuyisa imiphumela ehlukene kuye ngokuthi izihlungi zisetshenzisiwe. kanye nomklamo wethebula le-pivot.

Izinyathelo azenzi lutho kuze kube yilapho zisetshenziswa embikweni. Zigcinwa nemodeli yedatha futhi zivela ohlwini lwensimu lwamathebula e-pivot. ukuze noma yimuphi umsebenzisi wencwadi akwazi ukuzisebenzisa. Ziyisisekelo sokubala okuhlanganisiwe okuguquguqukayo, njengezilinganiso, amaphesenti omnikelo, izingqikithi ezihlanganisiwe, ukuqhathanisa phakathi kwezikhathi, njll.

Umehluko obalulekile phakathi kwemisebenzi ye-DAX nemisebenzi ye-Excel

Nakuba imisebenzi eminingi ye-DAX ifana nemisebenzi ye-Excel ngegama kanye nokuziphatha okuvamile, Azishintshi nje kuphela.Kunomehluko obalulekile othinta indlela amafomula akhiwa ngayo kumodeli ye-Power Pivot.

Okokuqala, I-DAX ayisebenzi ngamaseli noma ububanzi obuhlukileAmakholomu noma amathebula aphelele asetshenziswa njalo njengereferensi. Lokhu kukuphoqa ukuthi ucabange kakhulu ngamasethi edatha kunezinto ngazinye, okuhambisana kangcono nendlela yemodeli yedatha yobudlelwano.

Kuluhlu lwezinsuku, i-DAX iyabuya amanani ohlobo lwesikhathi sangempelaNakuba i-Excel ivame ukumela izinsuku njengezinombolo ezilandelanayo, lo mehluko usobala ezimweni eziningi, kodwa kubalulekile ukuwukhumbula lapho uhlanganisa amamodeli noma ungenisa idatha evela kwezinye izinhlelo.

Elinye iphuzu elibalulekile ukuthi Imisebenzi eminingi emisha ye-DAX ibuyisela amathebula aphelele (isibonelo, ISIHLUNGI, KONKE, AMANINGI, njll.) noma bamukela amathebula njengezimpikiswano. I-Excel, ngakolunye uhlangothi, ayinawo umqondo womsebenzi "obuyisela ithebula" ngomqondo ofanayo, yize amafomula e-array ekhona.

Ekugcineni, ku-DAX kucatshangwa ukuthi Wonke amanani kukholomu abelana ngohlobo olufanayo lwedathaUma izinhlobo zedatha zixutshwe, injini yedatha izophoqa ukuguqulwa kwekholomu yonke ibe uhlobo olufanelana kahle nawo wonke amarekhodi, okungase ngezinye izikhathi kuholele ekumangaleni uma imithombo yedatha ingahlolwanga kahle.

Izinhlobo zedatha ku-DAX kanye nohlobo lwethebula

Uma ungenisa ulwazi kumodeli ye-Power Pivot, Idatha iguqulwa ibe olunye lwezinhlobo zedatha ezisekelwa yinjini. (izinombolo, umbhalo, ama-boolean, izinsuku nezikhathi, uhlobo lwemali, njll.). Lolu hlobo lwedatha lunquma ukuthi yimiphi imisebenzi esebenzayo nokuthi amafomula azohlolwa kanjani.

Isici esisha esibalulekile uma siqhathaniswa ne-Excel yakudala yi- uhlobo lwedatha yethebulaImisebenzi eminingi ye-DAX yamukela ithebula lonke njengempikiswano bese ibuyisela elinye ithebula ngenxa yalokho. Isibonelo, i-FILTER ithatha ithebula kanye nesimo, bese ibuyisela ithebula elinemigqa ehlangabezana nesimo kuphela.

Ukuhlanganisa imisebenzi ebuyisela amatafula nge imisebenzi yokuhlanganisa efana ne-SUMX, AVERAGEX, noma i-MINXIzibalo eziyinkimbinkimbi kakhulu zingakhiwa ezisebenza kuma-subset edatha achazwe ngokuguquguqukayo. Lokhu kuphumela ekuhlanganisweni okwenziwe ngokwezifiso okuvumelana nezihlungi ezisebenzayo nganoma yisiphi isikhathi.

Ubudlelwano, umongo, kanye nemodeli yobudlelwano ku-Power Pivot

Iwindi le-Power Pivot yilapho kwakhiwa khona imodeli yedatha yobudlelwano. Lapho ungangenisa amatafula amaningi bese udala ubudlelwano phakathi kwawo. (isibonelo, Ukuthengisa Ngemikhiqizo, Ukuthengisa Ngekhalenda, Ukuthengisa Namakhasimende, njll.). Lobu budlelwano buyisisekelo samafomula e-DAX ukuze akwazi ukugxuma esuka kwelinye itafula aye kwelinye.

Uma amathebula ehlobene, Ungabhala amafomula anezela amanani avela kuthebula elihlobene bese uwasebenzisa kuthebula obhala kulo inkulumo. Ungalawula futhi ukuthi yimiphi imigqa ehlanganyela ekubalweni ngokusebenzisa izihlungi kumakholomu athile.

Kubalulekile ukuthi unake Yonke imigqa etafuleni le-Power Pivot kumele ibe nenani elifanayo lamakholomuIkholomu ngayinye kumele igcine uhlobo lwedatha oluhambisanayo kuyo yonke imigqa yayo. Uma okhiye bobudlelwano benamanani angalingani (izikhala, amanani angenazo izintandane, njll.), amafomula okubheka kanye namathebula e-pivot angaletha imiphumela engalindelekile.

Omunye umqondo oyisisekelo yi- umongoKu-DAX, amagama athi umongo womugqa kanye nomongo wesihlungi asetshenziswa kakhulu. Umongo womugqa uwumugqa "wamanje" lapho kuhlolwa khona ikholomu noma i-iterator ebaliwe; umongo wesihlungi uwuqoqo lwezihlungi ezisebenzayo (ezivela kuthebula le-pivot, izilayi, ubudlelwano, imisebenzi efana ne-CALCULATE, njll.).

Ngokudlala ngemisebenzi efana ne-CALCUTE, ALL, ALLEXCEPT, noma i-FILTER, ungakwazi Shintsha umongo wesihlungi ukuze ushintshe indlela isilinganiso esihlolwa ngayo.Lokhu kuvumela, isibonelo, ukubala iphesenti lokuthengiswa komkhiqizo uma kuqhathaniswa nenani eliphelele, noma ukuqhathanisa ukusebenza kwesigaba nenkampani iyonke ngaphandle kwezihlungi.

Ukuvuselelwa kwedatha kanye nokubalwa kabusha kwefomula ye-DAX

Kumodeli esebenzisa amafomula ayinkimbinkimbi noma idatha eningi, ukuqonda ukuthi isibuyekezo sisebenza kanjani kubalulekile. Kubalulekile ukuhlukanisa phakathi kokuvuselela idatha nokubala kabusha amafomula.okuyizinqubo ezihlobene kodwa ezizimele.

Ukubuyekezwa kwedatha kuqukethe ukuletha amarekhodi amasha encwadini evela emithonjeni yangaphandle (yolwazi(amafayela, izinsizakalo eziku-inthanethi, njll.). Ungaqalisa lesi sibuyekezo ngesandla uma kudingeka, noma usihlele uma incwadi ishicilelwe ku- I-SharePoint noma enye indawo ehambisanayo. Ngokuvamile le nqubo yenziwa kusetshenziswa Umbuzo Wamandla ku-Excel ukulungiselela nokuguqula idatha ngaphambi kokuyilayisha kumodeli.

Ngakolunye uhlangothi, ukubala kabusha inqubo lapho Amafomula e-DAX aphinde ahlolwe ukuze abonise izinguquko kudatha noma ezingxoxweni ngokwazo.Kumakholomu abaliwe, uma ushintsha ifomula, lonke ikholomu kumele liphinde libalwe ngesikhathi esisodwa. Kuma-schematics, ukubalwa kabusha kwenzeka lapho umongo uguqulwa (izihlungi, umugqa/amasimu ekholomu yethebula le-pivot) noma lapho amathebula e-pivot ebuyekezwa ngesandla.

Lokhu kubalwa kabusha kungathinta ukusebenza, ikakhulukazi uma kusetshenziswa amakholomu amaningi ayinkimbinkimbi noma imisebenzi ephindaphindayo ejulile kumathebula amakhuluNgakho-ke, umkhuba omuhle ukuhambisa iningi le-logic liye ezilinganisweni esikhundleni samakholomu, noma nini lapho kungenzeka.

Ukutholwa nokulungiswa kwamaphutha kumafomula e-DAX

Uma ubhala amafomula e-DAX, kuvamile ukuhlangana nezinhlobo ezintathu zamaphutha: amaphutha okubhala, amaphutha okubhala, namaphutha okubalaNgayinye inezimo zayo kanye nendlela yayo yokuzilungisa.

Amaphutha e-syntax alula kakhulu: abakaki abalahlekile, amakhoma abekwe kabi, amagama omsebenzi apelwe kabinjll. Usizo lokuqedela ngokuzenzakalela kanye nereferensi yomsebenzi we-DAX kukusindisa kulezi zingibe eziningi.

Amaphutha e-semantic kanye nokubala ayenzeka lapho, noma ngabe i-syntax ilungile, Ifomula yenza into engenangqondo kumongo wemodeli.Isibonelo, ukubhekisela kuthebula noma ikholomu engekho, ukudlulisa inani elingalungile lama-agumenti kumsebenzi, ukuxuba izinhlobo ezingahambisani, noma kuye ngekholomu enamaphutha angaphambilini.

Kulezi zimo, i-DAX ivame ukumaka Ikholomu yonke ibalwe njengengalungile.hhayi nje umugqa othize, ngoba ikholomu ibhekwa njengeyunithi. Uma ikholomu iqukethe i-metadata kuphela kodwa ingakacutshungulwa (ingenalo idatha elayishiwe), izobonakala impunga, futhi amafomula ancike kuyo ngeke akwazi ukuhlola kahle.

Icala elikhethekile amanani I-NaN (Akuyona Inombolo)Lezi zindinganiso zingavela, isibonelo, lapho kuhlukaniswa u-0 ngo-0. Uma ikholomu iqukethe amanani e-NaN, ukuhlunga noma ukuhlukanisa la manani kungaveza imiphumela engavamile ngoba amanani e-NaN awakwazi ukuqhathaniswa ngendlela evamile nezinye izinombolo. Ezimweni ezinjalo, kuyalulekwa ukusebenzisa izitatimende ze-IF noma eminye imisebenzi enengqondo ukuze kufakwe amanani e-NaN esikhundleni samanani e-0 noma elinye inani lezinombolo eliphathekayo.

Ukuhambisana namamodeli ethebula kanye nemodi ye-DirectQuery

Amafomula e-DAX owadala ku-Power Pivot, ngokuvamile, iyahambisana namamodeli ethebula le-SQL Server Analysis ServicesLokhu kusho ukuthi ungathutha imodeli yakho uye kuseva yethebula bese uqhubeka nokusebenzisa i-logic osuvele uyakhile.

Kodwa-ke, uma imodeli yethebula isetshenziswa kumodi I-DirectQueryKungase kuvele imikhawulo: Eminye imisebenzi ye-DAX ayisekelwa ngokuqondile kuzizindalwazi ezithile zobudlelwano. noma bangase babuyisele imiphumela ehluke kancane ngenxa yendlela imibuzo edluliselwa ngayo.

Kulezi zimo, kubalulekile ukubuyekeza amadokhumenti athile enjini yethebula kanye qinisekisa izinyathelo ezibalulekile ukuqinisekisa ukuthi ziyaqhubeka nokusebenza njengoba bekulindelekile ngemva kokusebenzisa i-DirectQuery.

Izimo ezisebenzayo: izibalo eziyinkimbinkimbi nge-CALCULATE kanye nezihlungi

Enye yamandla e-DAX yikhono layo lokwenza izibalo eziyinkimbinkimbi ezithembele ekuhlanganisweni okwenziwe ngokwezifiso kanye nezihlungi eziguquguqukayoImisebenzi ye-CALCULATE kanye ne-CALCULATETABLE ibalulekile kulolu hlobo lwesimo.

I-CALATE ivumela chaza kabusha umongo wesihlungi lapho kuhlolwa khona inkulumoIsibonelo, ungacela "isamba sokuthengisa esihlungiwe onyakeni othize, noma ngabe ithebula le-pivot libonisa eminye iminyaka," noma "isamba ngaphandle kokusebenzisa izihlungi ezithile zomkhiqizo."

Noma kuphi lapho umsebenzi we-DAX wamukela ithebula njengempikiswano, Ungadlula inguqulo ehlungiwe yalelo thebulaLokhu kungenziwa ngokusebenzisa i-FILTER noma ngokucacisa izimo ngaphakathi kwe-CALCULATE. Lokhu kwenza kube nokwenzeka ukwakha izindlela ezivumelana nezinkulungwane zezinhlanganisela zezimo ngaphandle kwesidingo sokudala amakholomu aphakathi.

Kungenzeka futhi susa ngokukhetha izihlungi ezikhona usebenzisa imisebenzi efana ne-ALL noma i-ALLEXCEPT. Isibonelo, ukuze ubale umnikelo womthengisi othile maqondana nenani eliphelele labathengisi, ungaba nesilinganiso sokuhlukanisa inani kumongo wamanje ngenani kumongo "WE-ALL" (ngaphandle kwezihlungi zomthengisi).

Kwezinye izimo, kuzodingeka usebenzise amanani "eluphu yangaphandle"Okusho ukuthi, ukubhekisela emuva kumongo womugqa wangaphambilini noma wokuphindaphinda. Yilapho imisebenzi efana ne-EARLIER ingena khona, evumela amazinga amabili ezihibe ezihlanganisiwe futhi iwusizo kakhulu ekudaleni amazinga, izingqikithi zamaqembu, noma izibalo ezincike kumongo womugqa wangaphambilini.

Ngisebenza ngombhalo, izinsuku, kanye nezihluthulelo ku-DAX

I-DAX inikeza namathuluzi amaningi okuthuthukisa shintsha umbhalo nezinsukuLokhu kubalulekile uma imithombo yedatha iqukethe izinsuku ezifomethi ezingavamile, okhiye abahlanganisiwe, noma izinkambu zombhalo ezidinga ukuguqulwa zibe amanani esikhathi.

I-Power Pivot ayibasekeli ngokuqondile okhiye abahlanganisiwe ebuhlotsheniUma umthombo wakho usebenzisa amakholomu amaningi njengezihluthulelo, ezimweni eziningi kuzodingeka udale ikholomu ebaliwe ehlanganisa lezo zingxenye zibe ukhiye owodwa futhi uyisebenzise njengensimu yobudlelwano.

Uma izinsuku zifika ngamafomethi angabonwa yinjini (isibonelo, usuku olufomethi yesifunda engavamile noma inombolo ephelele efana no-01032009 engeniswe njengombhalo), ungakha amafomula anjengalawa:

=DATE(KWESOKUDLA(,4), KWESOKUNXELE(,2), PHAKATHI(,3,2))

Ngalolu hlobo lokuveza, Wakha kabusha usuku olusebenzayo lwe-SQL Server kusuka ezingcezwini ezikhishwe ku-string, okukuvumela ukuthi usebenzise imisebenzi yokuhlakanipha kwesikhathi ngaphandle kwezinkinga.

Ungakwazi futhi shintsha izinhlobo zedatha usebenzisa amafomulaPhindaphinda ngo-1,0 ukuze uguqule izinsuku noma izintambo zezinombolo zibe izinombolo, noma uhlangane nentambo engenalutho ukuze uguqule inombolo noma usuku lube umbhalo. Ngaphezu kwalokho, kunemisebenzi ethile yokulawula uhlobo lokubuyisa (ukunciphisa amadesimali, ukuphoqa izinombolo, njll.).

Amanani anemibandela kanye nokuphathwa kwamaphutha kumakholomu nasezilinganisweni

Njengaku-Excel, i-DAX ifaka imisebenzi ye- buyisela imiphumela ngokusekelwe ezimweni kanye nokusingatha amaphutha ngobuciko. Isibonelo, ungabiza abathengisi ngokuthi “Okuthandwayo” noma “Inani” ngokusekelwe kumthamo wabo wokuthengisa waminyaka yonke usebenzisa izitatimende ze-IF ezihlanganisiwe.

Nokho, kukholomu ebaliwe, Awukwazi ukuba nezingxabano ezithile ezinamaphutha kanti ezinye azinawo.Uma umugqa owodwa uveza iphutha, lonke ikholomu limakwa njengelingalungile. Lokhu kudinga ukulawulwa kwamaphutha okuqinile kunasesipredishithini esivamile.

Ukuze kuvinjelwe ukuhlukaniswa okulula ngo-zero noma inani elingenalutho ekuphahlazeni ikholomu yonke, kunconywa songa imisebenzi ebucayi ekuhlolweni kwangaphambilini kusetshenziswa imisebenzi ye-IF kanye nolwazi, njalo kubuyisela inani elivumelekile ngisho noma inhlanganisela yedatha ingajwayelekile.

Uma wakha imodeli, ingaba wusizo Vumela amaphutha avele ekuqaleni ukuze ukwazi ukuwathola nokuwalungisa.Kodwa uma usuyishicilele abanye abasebenzisi, kubalulekile ukuqinisekisa ukuthi amafomula aphephile nokuthi akukho myalezo wephutha ovele kumathebula e-pivot noma ku-visualizations.

Ukuhlakanipha kwesikhathi: izingqikithi ezihlanganisiwe, ukuqhathanisa, kanye nezikhathi ezenziwe ngokwezifiso

Izici zobuhlakani besikhathi zingenye yezinto ezinhle kakhulu ze-DAX. Zikuvumela ukuthi usebenze ngamadethi ahlukene, ubale izingqikithi ezihlanganisiwe, uqhathanise izikhathi, futhi udale amafasitela esikhathi ngokwezifiso. kalula, uma nje unethebula lekhalenda elilungiselelwe kahle.

Kungadalwa izinyathelo ukuthengisa okuhlanganisiwe ngosuku, ngenyanga, ngekota noma ngonyakaBala izilinganiso zokuvula nezokuvala zesikhathi ngasinye, noma uqhathanise ukuthengisa kusukela onyakeni owodwa kuya onyakeni odlule, ikota kuya kwekota, njll., usebenzisa imisebenzi ethile yesikhathi.

Ngaphezu kwalokho, ungalulama amasethi ezinsuku ngokwezifisonjengokuthi "izinsuku zokuqala ezingu-15 ngemva kokuqala kokukhushulwa" noma "isikhathi esifanayo ngonyaka odlule," bese kudluliselwa lokho kusethwe kumsebenzi ohlanganisa idatha ngalelo fasitela losuku oluthile.

Imisebenzi efana ne-PARALLELPERIOD neminye ehlobene nezikhathi ezihambisanayo Zenza kube lula ukuqhathanisa phakathi kwezikhawu ezishintshashintsha isikhathi.Isibonelo, ukuhlaziya ukuthi umkhankaso uthuthukise imiphumela yini uma kuqhathaniswa nesikhathi esifanayo komunye unyaka.

Ukubeka ezingeni kanye nokuqhathaniswa kwamanani: amazinga aphezulu angu-N kanye nezinga eliguqukayo

Uma udinga ukukhombisa izinto ezifanele kakhulu kuphela (isibonelo, imikhiqizo eyi-10 ethengiswa kakhulu), unezindlela ezimbili eziyinhloko: sebenzisa imisebenzi yokuhlunga ye-Excel kuthebula le-pivot noma wakhe izinga eliguquguqukayo nge-DAX.

I-Excel inikeza izihlungi zohlobo "eziyi-10 eziphezulu" kumathebula e-pivot, kulula kakhulu ukusetha Ukubonisa izinto ezingenhla noma ezingezansi kuphela inkambu yezinombolo enikeziwe. Ungahlunga ngenani lezinto, iphesenti elihlanganisiwe, noma isamba samanani.

Inkinga ngale ndlela ukuthi Isihlungi senzelwe ukwethula kuphela.Uma idatha eyisisekelo ishintsha, udinga ukubuyekeza ithebula le-pivot ngesandla ukuze isihlungi sibonakale kahle. Ngaphezu kwalokho, awukwazi ukusebenzisa kabusha leso simo njengengxenye yamanye amafomula e-DAX.

Okunye ongakwenza ukudala eyodwa ikholomu ebaliwe noma isilinganiso esinikeza izinga ku-element ngayinye kusetshenziswa i-DAX. Le nketho ibiza kakhulu ngokwezibalo, kodwa inezinzuzo: izinga libalwa kabusha ngokuguquguqukayo futhi lingasetshenziswa kuma-data slicer, okuvumela umsebenzisi ukuthi akhethe ukuthi ufuna ukubona ama-top 5, ama-top 10, ama-top 50, njll.

Noma kunjalo, kumamodeli anezigidi zemigqa, Ukuhlela okunamandla kungaba nzima. futhi kuyadingeka ukuhlola ukuthi izindleko zokusebenza ziyancishiswa yini yinzuzo yokusebenza abayinikezayo.

Izindlela ezinhle kakhulu zokuklama amamodeli e-DAX ku-Power Pivot

Ukuze imodeli ye-DAX ku-Power Pivot ihlale isesimweni futhi isebenze kahle, akwanele ukuthi amafomula "asebenze". Kunconywa ukulandela uchungechunge lwemikhuba emihle lokho kwenza umehluko omkhulu kumaphrojekthi angempela.

Esinye isincomo esiphindaphindayo sithi beka phambili izinyathelo ngaphezu kwamakholomu abaliwe lapho ukubala empeleni kuyinhlanganisela eguquguqukayo futhi akuyona imfanelo egxilile. Amakholomu abaliwe athatha inkumbulo futhi abalwa kabusha wonke ngesikhathi esisodwa, kuyilapho izilinganiso zihlolwa kuphela uma kudingeka.

Ibuye isebenziseke kakhulu kusetshenziswa iziguquguquko ku-DAX (VAR) Ukuze kube lula ukwenza amafomula ayinkimbinkimbi, gwema ukuphinda ukubala okufanayo izikhathi eziningi, futhi uthuthukise ukufundeka. Lokhu kusiza kokubili ukusebenza kanye nokuqonda imodeli lapho omunye umuntu eyibuyekeza.

Okokugcina, amagama acacile kanye nemibhalo yangaphakathi emincane kwenza umehluko omkhulu. Nikeza amagama achazayo ezilinganisweni nasemakholomuGwema izifinyezo ezingacacile futhi ubhale phansi amafomula abaluleke kakhulu. Lokhu kunciphisa ijika lokufunda labasebenzisi abasha futhi kukusindise ezinhlungwini zekhanda uma ubuyela kumodeli ezinyangeni ezizayo.

Ukwazi i-DAX ngaphakathi kwe-Power Pivot akukhona ukukhumbula yonke imisebenzi, kodwa kumayelana nokuqonda ukuthi amafomula asebenzisana kanjani nemodeli yobudlelwano, umongo wesihlungi, kanye nezibuyekezo zedatha. Ngesisekelo esiqinile kumakholomu abaliwe, izilinganiso, imisebenzi yesikhathi, ukuphathwa kwamaphutha, kanye nemikhuba emihle yokuklamaAmamodeli akho ethebula aba nezimo eziguquguqukayo, kulula kakhulu ukuwahlaziya, futhi ngaphezu kwakho konke, akwazi ukuphendula imibuzo yebhizinisi eyinkimbinkimbi ngokumane ahudule amasimu ambalwa etafuleni le-pivot.