Data Analysis Expressions (DAX) in Power Pivot

Wat zijn DAX-formules?

DAX-formules vertonen sterke overeenkomsten met Excel-formules. Als u een formule wilt maken, typt u een isgelijkteken, gevolgd door een functienaam of expressie en eventueel waarden of argumenten. Net als Excel bevat DAX verschillende functies waarmee u met tekenreeksen kunt werken, berekeningen met datums en tijden kunt uitvoeren of voorwaardelijke waarden kunt maken.

DAX-formules zijn echter aanzienlijk verschillend:

Als u berekeningen per rij wilt aanpassen, biedt DAX functies waarmee u de huidige rijwaarde of een gerelateerde waarde kunt gebruiken om berekeningen uit te voeren die naar gelang de context variëren.
DAX bevat een nieuw type functie waarmee een tabel het resultaat is in plaats van één waarde. U kunt met deze functies invoer leveren voor andere functies.
Time Intelligence-functies in DAX berekeningen met datumbereiken toestaan en de resultaten over parallelle perioden vergelijken.

Waar gebruikt u DAX-formules?

U kunt in Power Pivot formules maken in berekende kolommen of in berekende velden.

Berekende kolommen

Een berekende kolom is een kolom die u toevoegt aan een bestaande Power Pivot-tabel. In plaats van de waarden in de kolom te plakken of te importeren, maakt u een DAX-formule waarmee de kolomwaarden worden gedefinieerd. Als u de Power Pivot-tabel opneemt in een draaitabel (of draaigrafiek), kan de berekende kolom op dezelfde manier als andere gegevenskolommen worden gebruikt.

De formules in berekende kolommen lijken veel op de formules die u in Excel maakt. In tegenstelling tot excel kunt u echter geen andere formule maken voor verschillende rijen in een tabel. In plaats daarvan wordt de DAX-formule automatisch toegepast op de hele kolom.

Wanneer een kolom een formule bevat, wordt voor elke rij de waarde berekend. Zodra u de formule maakt, worden de resultaten voor de kolom berekend. Kolomwaarden worden alleen herberekend als de onderliggende gegevens worden vernieuwd of als handmatige herberekening wordt gebruikt.

U kunt berekende kolommen maken die zijn gebaseerd op metingen en andere berekende kolommen. Vermijd echter het gebruik van dezelfde naam voor een berekende kolom en een meting, omdat dit tot verwarrende resultaten kan leiden. Wanneer u naar een kolom verwijst, kunt u het beste een volledig gekwalificeerde kolomverwijzing gebruiken om te voorkomen dat u per ongeluk een meting aanroept.

Zie Berekende kolommen in Power Pivot voor meer informatie.

Metingen

Een meting is een formule die specifiek is gemaakt voor gebruik in een draaitabel (of draaigrafiek) die gebruikmaakt van Power Pivot gegevens. Metingen kunnen worden gebaseerd op standaardaggregatiefuncties, zoals AANTAL of SOM, of u kunt uw eigen formule definiëren met behulp van DAX. Een meting wordt gebruikt in het gebied Waarden van een draaitabel. Als u berekende resultaten in een ander gebied van een draaitabel wilt plaatsen, gebruikt u in plaats hiervan een berekende kolom.

Wanneer u een formule voor een expliciete meting definieert, gebeurt er niets totdat u de meting toevoegt aan een draaitabel. Wanneer u de meting toevoegt, wordt de formule geëvalueerd voor elke cel in het gebied Waarden van de draaitabel. Omdat er een resultaat wordt gemaakt voor elke combinatie van rij- en kolomkoppen, kan het resultaat voor de meting in elke cel verschillen.

De definitie van de meting die u maakt, wordt opgeslagen met de bijbehorende brongegevenstabel. De definitie wordt weergegeven in de lijst met draaitabelvelden en is voor alle gebruikers van de werkmap beschikbaar.

Zie Metingen in Power Pivot voor meer gedetailleerde informatie.

Formules maken met de formulebalk

Power Pivot bevat net als Excel, een formulebalk om het maken en bewerken van formules te vereenvoudigen, en de functie Automatisch aanvullen om type- en syntaxisfouten tot een minimum te beperken.

De naam van een tabel opgeven Begin de naam van de tabel te typen. Voor Automatisch aanvullen wordt een vervolgkeuzelijst weergegeven met geldige namen die met die letters beginnen.

De naam van een kolom opgeven Typ een vierkante haak en kies vervolgens de kolom in de lijst met kolommen in de huidige tabel. Voor een kolom in een andere tabel begint u de eerste letters van de tabelnaam te typen en kiest u vervolgens de kolom in de vervolgkeuzelijst van Automatisch aanvullen.

Zie Formules maken voor berekeningen in Power Pivot voor meer informatie en stapsgewijze instructies voor het maken van formules.

Tips voor het gebruik van Automatisch aanvullen

U kunt Formule automatisch aanvullen gebruiken in het midden van een bestaande formule met geneste functies. De tekst direct vóór de invoegpositie wordt gebruikt om waarden weer te geven in de vervolgkeuzelijst en alle tekst na de invoegpositie blijft ongewijzigd.

Gedefinieerde namen die u voor constanten maakt, worden niet in de vervolgkeuzelijst van Automatisch aanvullen weergegeven, maar u kunt ze wel typen.

Power Pivot voegt niet het haakje sluiten van functies toe of komt niet automatisch overeen met haakjes. Zorg ervoor dat elke functie syntactisch correct is of dat u de formule niet kunt opslaan of gebruiken.

Meerdere functies in een formule gebruiken

U kunt functies nesten, wat betekent dat u de resultaten van een functie kunt gebruiken als argument voor een andere functie. U kunt functies in berekende kolommen tot 64 niveaus diep nesten. Het maken van formules of het oplossen van problemen wordt echter wel bemoeilijkt door het nesten.

Veel DAX-functies zijn uitsluitend bedoeld voor gebruik als geneste functies. Deze functies resulteren in een tabel, die niet direct kan worden opgeslagen als resultaat, maar dient als invoer voor een tabelfunctie. Voor de functies SUMX, AVERAGEX en MINX is bijvoorbeeld een tabel als eerste argument vereist.

Opmerking: Er bestaan enkele limieten voor het nesten van functies binnen metingen, om ervoor te zorgen dat de prestaties niet worden beïnvloed door de vele berekeningen die zijn vereist door afhankelijkheden tussen kolommen.

DAX-functies en Excel-functies vergelijken

De DAX-functiebibliotheek is gebaseerd op de Excel-functiebibliotheek, maar de bibliotheken hebben veel verschillen. In dit gedeelte vindt u een overzicht van de verschillen en overeenkomsten tussen Excel-functies en DAX-functies.

Veel DAX-functies hebben dezelfde naam en hetzelfde gedrag als Excel-functies, maar zijn aangepast voor verschillende typen invoer en kunnen in sommige gevallen een ander gegevenstype retourneren. In het algemeen kunt u geen DAX-functies gebruiken in een Excel-formule of Excel-formules in Power Pivot zonder aanpassingen te verrichten.
DAX-functies gebruiken geen celverwijzing of of -bereik als verwijzing, maar een kolom of tabel.
Datum- en tijdfuncties van DAX retourneren een datetime-gegevenstype. Datum- en tijdfuncties in Excel resulteren daarentegen in een geheel getal dat staat voor een datum als een serieel getal.
Veel van de nieuwe DAX-functies retourneren een tabel met waarden of maken berekeningen op basis van een tabel met waarden als invoer. Excel heeft daarentegen geen functies die een tabel retourneren, maar sommige functies kunnen wel met matrices werken. De mogelijkheid om eenvoudig naar volledige tabellen en kolommen te verwijzen, is nieuw in Power Pivot.
DAX heeft nieuwe lookup-functies die vergelijkbaar zijn met de matrix- en vectorlookup-functies in Excel. Voor de DAX-functies is het echter vereist dat er een relatie tussen de tabellen tot stand komt.
De gegevens in een kolom moeten altijd van hetzelfde gegevenstype zijn. Als de gegevens niet van hetzelfde type zijn, wijzigt DAX de hele kolom in het gegevenstype dat het beste bij alle waarden past.

DAX-gegevenstypen

U kunt gegevens in een Power Pivot-gegevensmodel importeren uit veel verschillende gegevensbronnen die verschillende gegevenstypen kunnen ondersteunen. Wanneer u de gegevens importeert of laadt en ze vervolgens in berekeningen of in draaitabellen gebruikt, worden de gegevens geconverteerd naar een van de Power Pivot-gegevenstypen. Zie Gegevenstypen in gegevensmodellen voor een lijst met de gegevenstypen.

Het tabelgegevenstype is een nieuw gegevenstype in DAX en wordt gebruikt als de in- of uitvoer voor veel nieuwe functies. De functie FILTER gebruikt bijvoorbeeld een tabel als invoer en voert een andere tabel uit met alleen de rijen die aan de filtervoorwaarden voldoen. Door tabelfuncties te combineren met aggregatiefuncties kunt u complexe berekeningen uitvoeren op dynamisch gedefinieerde gegevenssets. Zie Aggregaties in Power Pivot voor meer informatie.

Formules en het relationele model

Het Power Pivot-venster is een gebied waarin u met verschillende gegevenstabellen kunt werken en de tabellen in een relationeel model kunt verbinden. Binnen dit gegevensmodel worden tabellen aan elkaar verbonden door relaties, waarmee u correlaties met kolommen in andere tabellen kunt maken voor interessantere berekeningen. U kunt bijvoorbeeld formules maken waarmee de som van de waarden van een gerelateerde tabel wordt berekend en vervolgens die waarde in één cel opslaan. Of u kunt filters op tabellen en kolommen toepassen om de rijen van de gerelateerde tabel te beheren. Zie Relaties tussen tabellen in een gegevensmodel voor meer informatie.

Aangezien u met relaties tabellen kunt koppelen, kunnen uw draaitabellen ook gegevens uit meerdere kolommen bevatten die van verschillende tabellen afkomstig zijn.

Omdat formules echter met volledige tabellen en kolommen kunnen werken, moet u berekeningen anders ontwerpen dan u in Excel zou doen.

In het algemeen wordt een DAX-formule in een kolom altijd toegepast op de gehele set waarden in de kolom (nooit slechts op enkele rijen of cellen).
Tabellen in Power Pivotmoeten altijd hetzelfde aantal kolommen in elke rij hebben, en alle rijen in een kolom moeten hetzelfde gegevenstype bevatten.
Wanneer tabellen via een relatie zijn verbonden, wordt van u verwacht dat u controleert of de twee kolommen die als sleutels worden gebruikt, waarden hebben die grotendeels overeenkomen. Aangezien in Power Pivot geen referentiële integriteit wordt afgedwongen, is het mogelijk dat u niet-overeenkomende waarden in een sleutelkolom hebt en toch een relatie kunt maken. De aanwezigheid van lege of niet-overeenkomende waarden kan echter van invloed zijn op de resultaten van formules en het uiterlijk van draaitabellen. Zie Lookups in Power Pivot-formules.
Wanneer u tabellen koppelt via relaties, vergroot u het bereik of de context waarin uw formules worden geëvalueerd. Formules in een draaitabel kunnen bijvoorbeeld worden beïnvloed door filters of kolom- en rijkoppen in de draaitabel. U kunt formules schrijven waarmee de context wordt gemanipuleerd, maar de context kan er ook voor zorgen dat uw resultaten op een onverwachte manier worden gewijzigd. Zie Context in DAX-formules voor meer informatie.

Resultaten van formules bijwerken

Gegevensvernieuwing en herberekening zijn twee afzonderlijke, maar gerelateerde bewerkingen die u moet kennen wanneer u een gegevensmodel ontwerpt dat ingewikkelde formules en grote hoeveelheden gegevens bevat, of gegevens bevat die uit externe gegevensbronnen worden opgehaald.

Gegevensvernieuwing is het proces waarmee gegevens in uw werkmap worden bijgewerkt met nieuwe gegevens uit een externe gegevensbron. U kunt gegevens handmatig vernieuwen na een door u opgegeven interval. Of u kunt een automatische vernieuwing van externe bronnen plannen als u de werkmap naar een SharePoint-site hebt gepubliceerd.

Herberekening is het proces waarmee de resultaten van formules worden bijgewerkt om wijzigingen in de formules zelf en deze wijzigingen in de onderliggende gegevens door te voeren. Herberekening kan op de volgende manieren de prestaties beïnvloeden:

Wanneer u een formule wijzigt, moet u voor een berekende kolom de resultaten van de formule altijd voor de hele kolom herberekenen.
Voor een meting worden de resultaten van een formule pas berekend nadat de meting in de context van de draaitabel of draaigrafiek is geplaatst. De formule wordt ook herberekend wanneer u een rij- of kolomkop wijzigt die filters voor de gegevens beïnvloedt, of wanneer u handmatig de draaitabel vernieuwt.

Problemen met formules oplossen

Fouten bij het schrijven van formules

Als er bij het definiëren van een formule een fout wordt weergegeven, bevat de formule mogelijk een syntactische fout, semantische fout of berekeningsfout.

Syntactische fouten zijn het gemakkelijkst op te lossen. Meestal ontbreekt er een haakje of komma. Raadpleeg de naslaginformatie over de functies voor expressies voor gegevensanalyse voor hulp bij de syntaxis van afzonderlijke functies.

Het andere type fout doet zich voor wanneer de syntaxis juist is, maar de waarde of de kolom waarnaar wordt verwezen niet klopt in de context van de formule. Dergelijke semantische en berekeningsfouten kunnen door de volgende problemen worden veroorzaakt:

In de formule wordt verwezen naar een niet-bestaande kolom, tabel of functie.
De formule lijkt correct, maar wanneer de gegevensengine de gegevens ophaalt, wordt vastgesteld dat de typen niet overeenkomen en wordt er een fout weergegeven.
Met de formule wordt een verkeerd aantal parameters of parameters van het verkeerde type aan een functie doorgegeven.
De formule verwijst naar een andere kolom die een fout bevat, en daarom zijn de waarden van de formule ongeldig.
De formule verwijst naar een kolom die nog niet is verwerkt, wat wil zeggen dat deze metagegevens bevat maar geen werkelijke gegevens die voor berekeningen kunnen worden gebruikt.

In de eerste vier gevallen markeert DAX de hele kolom die de ongeldige formule bevat. In het laatste geval geeft DAX de kolom grijs weer om aan te geven dat de kolom niet is verwerkt.

Onjuiste of ongebruikelijke resultaten bij het bepalen van de rangorde of sortering van kolomwaarden

Wanneer u de rangorde van waarden bepaalt of de waarden sorteert in een kolom die de waarde Geen getal bevat, krijgt u mogelijk verkeerde of onverwachte resultaten. Wanneer er bij een berekening bijvoorbeeld 0 door 0 wordt gedeeld, wordt een resultaat Geen getal geretourneerd.

De formule-engine bepaalt de rangorde en sortering namelijk door de numerieke waarden te vergelijken, maar Geen getal kan niet met andere getallen in de kolom worden vergeleken.

Voor correcte resultaten kunt u voorwaardelijke instructies met de functie ALS gebruiken om te testen op de waarde Geen getal en een numerieke waarde 0 te retourneren.

Compatibiliteit met tabelmodellen in Analysis Services en de DirectQuery-modus

Over het algemeen zijn de DAX-formules die u in Power Pivot hebt gemaakt, volledig compatibel met de tabelmodellen van Analysis Services. Als u echter uw Power Pivot-model migreert naar een Analysis Services-exemplaar en het model vervolgens implementeert in de DirectQuery-modus, zijn er enkele beperkingen.

Sommige DAX-formules kunnen verschillende resultaten retourneren wanneer u het model implementeert in de DirectQuery-modus.
Sommige formules kunnen validatiefouten veroorzaken wanneer u het model implementeert in de DirectQuery-modus, omdat de formule een DAX-functie bevat die niet wordt ondersteund in een relationele gegevensbron.

Zie de documentatie over tabelmodellen in Analysis Services in SQL Server 2012 Books Online voor meer informatie.