Excel DAX: utilizzo di testo e date
DAX può essere usato negli scenari che prevedono l'utilizzo di testo, l'estrazione e la composizione di valori di data e ora o la creazione di valori in base a una condizione. Puoi eseguire le seguenti operazioni con DAX:
- Crea una colonna chiave in una tabella per concatenazione.
- Comporre una data in base alle parti della data estratte da una data di testo.
- Definisci un formato data personalizzato.
- Modificare i tipi di dati utilizzando una formula.
- Converti numeri reali in interi.
- Converti numeri reali, interi o date in stringhe.
- Converti stringhe in numeri o date reali.
Creazione di una colonna chiave per concatenazione
Il modello di dati in PowerPivot consente solo una singola colonna chiave. Non supporta chiavi composite che potresti trovare nelle origini dati esterne. Pertanto, se esistono chiavi composte in una tabella nell'origine dati, è necessario combinarle in un'unica colonna di chiavi per la tabella nel modello di dati.
È possibile utilizzare la funzione DAX CONCATENATE per combinare due colonne in una singola colonna in una tabella nel modello di dati. La funzione DAX CONCATENATE unisce due stringhe di testo in una stringa di testo. Gli elementi uniti possono essere testo, numeri o valori booleani rappresentati come testo o una combinazione di questi elementi. È inoltre possibile utilizzare un riferimento di colonna se la colonna contiene valori appropriati.
= CONCATENATE ([Column1], [Column2])La funzione DAX CONCATENATE accetta solo due argomenti. Se uno degli argomenti non è di tipo di dati di testo, verrà convertito in testo. La funzione DAX CONCATENATE restituisce la stringa concatenata.
Data basata su parti di data estratte da una data di testo
Il modello di dati in Power Pivot supporta un tipo di dati datetime per i valori di data e ora. Le funzioni DAX che funzionano su valori di data e / o ora richiedono il tipo di dati datetime per gli argomenti.
Se l'origine dati contiene date di un formato diverso, è necessario prima estrarre le parti della data usando una formula DAX e combinare tali parti per costituire un tipo di dati datetime DAX valido.
È possibile utilizzare le seguenti funzioni DAX per estrarre e comporre le date:
DATE - Restituisce la data specificata nel formato datetime.
DATEVALUE - Converte una data sotto forma di testo in una data in formato datetime.
TIMEVALUE - Converte un'ora in formato testo in un'ora in formato datetime.
Definizione di un formato data personalizzato
Supponiamo che le date nell'origine dati non siano rappresentate da un formato standard. È possibile definire un formato di data personalizzato per garantire che i valori vengano gestiti correttamente. La funzione DAX FORMAT consente di convertire un valore in testo in base al formato specificato.
FORMAT (<value>, <format_string>)La funzione FORMAT restituisce una stringa contenente un valore formattato come definito da format_string.
È possibile utilizzare formati di data e ora predefiniti oppure creare formati di data e ora definiti dall'utente per l'argomento format_string della funzione FORMAT.
Di seguito sono riportati i nomi dei formati di data e ora predefiniti. Se utilizzi stringhe diverse da queste stringhe predefinite, verranno interpretate come un formato di data e ora personalizzato.
| S. No. | Format_String & Description |
|---|---|
| 1 | "General Date" Visualizza una data e / o un'ora. Ad esempio, 2/10/2015 10:10:32 AM |
| 2 | "Long Date" or "Medium Date" Visualizza una data in base al formato data lunga. Ad esempio, mercoledì 7 marzo 2016 |
| 3 | "Short Date" Visualizza una data utilizzando il formato data breve. Ad esempio, 2/03/2016 |
| 4 | "Long Time" Visualizza un'ora utilizzando il formato ora estesa. In genere include ore, minuti e secondi. Ad esempio, 10:10:32 AM |
| 5 | "Medium Time" Visualizza l'ora nel formato 12 ore. Ad esempio, alle 21:30 |
| 6 | "Short Time" Visualizza l'ora nel formato 24 ore. Ad esempio, 14:15 |
In alternativa, è possibile utilizzare i caratteri nella tabella seguente per creare formati di data / ora definiti dall'utente.
| S. No. | Carattere e descrizione |
|---|---|
| 1 | : Separatore di tempo. Separatore di tempo. Separa ore, minuti e secondi quando i valori dell'ora vengono formattati. |
| 2 | / Separatore di data. Separa il giorno, il mese e l'anno in cui vengono formattati i valori della data. |
| 3 | % Utilizzato per indicare che il carattere seguente deve essere letto come un formato di una sola lettera indipendentemente dalle lettere finali. Utilizzato anche per indicare che un formato di una sola lettera viene letto come formato definito dall'utente. |
Di seguito sono riportati i dettagli dei vari personaggi.
%d - Visualizza il giorno come numero senza zero iniziale (es. 5).
%dd - Visualizza il giorno come numero con uno zero iniziale (es. 05).
%ddd - Visualizza il giorno come abbreviazione (es. Sole).
%dddd - Visualizza il giorno come nome completo (ad es. Domenica).
%M - Visualizza il mese come numero senza zero iniziale (ad esempio, gennaio è rappresentato come 1).
%MM - Visualizza il mese come un numero con uno zero iniziale (ad esempio, gennaio è rappresentato come 01).
%MMM - Visualizza il mese come abbreviazione (ad esempio, gennaio è rappresentato come gennaio).
%MMMM - Visualizza il mese come nome completo del mese (ad es. Gennaio).
%gg - Visualizza la stringa del periodo / era (ad es. AD).
%h- Visualizza l'ora come numero senza zeri iniziali utilizzando l'orologio di 12 ore (ad esempio 1:15:15 PM). Uso%h se questo è l'unico carattere nel formato numerico definito dall'utente.
%hh - Visualizza l'ora come un numero con zeri iniziali utilizzando l'orologio di 12 ore (ad esempio 01:15:15 PM).
%H- Visualizza l'ora come numero senza zeri iniziali utilizzando l'orologio a 24 ore (ad esempio 13:15:15, 1:15:15). Usa% H se questo è l'unico carattere nel formato numerico definito dall'utente.
%HH - Visualizza l'ora come numero con zeri iniziali utilizzando l'orologio a 24 ore (ad esempio 13:15:15, 1:15:15).
%m- Visualizza i minuti come un numero senza zeri iniziali (ad esempio 2: 1: 15). Usa% m se questo è l'unico carattere nel formato numerico definito dall'utente.
%mm - Visualizza i minuti come un numero con zeri iniziali (ad esempio 2:01:15).
%s- Visualizza il secondo come numero senza zeri iniziali (ad esempio 2: 15: 5). Usa% s se questo è l'unico carattere nel formato numerico definito dall'utente.
%ss - Visualizza il secondo come numero con zeri iniziali (ad esempio 2:15:05).
%f- Visualizza frazioni di secondo. Per esff visualizza centesimi di secondo, mentre ffffvisualizza i decimillesimi di secondo. Puoi utilizzarne fino a settefsimboli nel formato definito dall'utente. Uso%f se questo è l'unico carattere nel formato numerico definito dall'utente.
%t- Utilizza l'orologio a 12 ore e visualizza una A maiuscola per qualsiasi ora prima di mezzogiorno; visualizza una P maiuscola per qualsiasi ora tra mezzogiorno e le 23:59. Utilizza% t se questo è l'unico carattere nel formato numerico definito dall'utente.
%tt- Per i paesi che utilizzano un orologio di 12 ore, visualizza un AM maiuscolo con qualsiasi ora prima di mezzogiorno; visualizza un PM maiuscolo con qualsiasi ora compresa tra mezzogiorno e le 23:59. Per i paesi che utilizzano un orologio a 24 ore, non visualizza nulla.
%y- Visualizza il numero dell'anno (0-9) senza zeri iniziali. Uso%y se questo è l'unico carattere nel formato numerico definito dall'utente.
%yy - Visualizza l'anno in formato numerico a due cifre con uno zero iniziale, se applicabile.
%yyy - Visualizza l'anno in formato numerico a quattro cifre.
%yyyy - Visualizza l'anno in formato numerico a quattro cifre.
%z- Visualizza la differenza di fuso orario senza uno zero iniziale (ad es. -8). Uso%z se questo è l'unico carattere nel formato numerico definito dall'utente.
%zz- Visualizza il file. offset del fuso orario con uno zero iniziale (ad esempio -08)
%zzz - Visualizza la differenza di fuso orario completo (es. -08: 00).
Come puoi osservare, le stringhe di formattazione fanno distinzione tra maiuscole e minuscole. È possibile ottenere una formattazione diversa utilizzando un case diverso.
Modifica dei tipi di dati degli output delle formule DAX
Nelle formule DAX, il tipo di dati dell'output è determinato dalle colonne di origine e non è possibile specificare in modo esplicito il tipo di dati del risultato. Questo perché il tipo di dati ottimale è determinato da PowerPivot. Tuttavia, è possibile usare le conversioni del tipo di dati implicite eseguite da Power Pivot per manipolare il tipo di dati di output. In caso contrario, puoi usare determinate funzioni DAX per convertire il tipo di dati di output.
Using the Implicit Data Type Conversions
Per convertire una data o una stringa numerica in un numero, moltiplicare per 1,0. Ad esempio, = (TODAY () + 5) * 1.0. Questa formula calcola la data corrente più 5 giorni e converte il risultato in un valore intero.
Per convertire una data, un numero o un valore di valuta in una stringa, concatenare il valore con una stringa vuota. Ad esempio, = Today () & ""
Using the DAX Functions for Data Type Conversions
È possibile utilizzare le funzioni DAX per quanto segue:
- Conversione di numeri reali in interi.
- Conversione di numeri reali, numeri interi o date in stringhe.
- Conversione di stringhe in numeri reali o date.
Lo imparerai nelle sezioni seguenti.
Conversione di numeri reali in interi
È possibile utilizzare le seguenti funzioni DAX per convertire i numeri reali in interi:
ROUND (<number>, <num_digits>) - Arrotonda un numero al numero di cifre specificato e restituisce un numero decimale.
CEILING (<number>, <significance>) - Arrotonda un numero per eccesso al numero intero più vicino o al multiplo significativo più vicino e restituisce un numero decimale.
FLOOR (<number>, <significance>) - Arrotonda un numero per difetto, verso lo zero, al multiplo significativo più vicino e restituisce un numero decimale.
Conversione di numeri reali, interi o date in stringhe
È possibile utilizzare le seguenti funzioni DAX per convertire numeri reali, interi o date in stringhe:
FIXED (<number>, [<decimals>], [<no_comma>])- Arrotonda un numero e restituisce il risultato come testo. Il numero di cifre a destra del separatore decimale è 2 o il numero di decimali specificato. Il risultato è con virgole o facoltativamente senza virgole.
FORMAT (<value>, <format_string>) - Converte un valore in testo in base al formato specificato.
Hai già imparato a usare la funzione Formato per convertire le date in stringhe.
Conversione di stringhe in numeri reali o date
È possibile utilizzare le seguenti funzioni DAX per convertire le stringhe in numeri o date reali:
VALUE (<text>) - Converte una stringa di testo che rappresenta un numero in un numero.
DATEVALUE (date_text) - Converte una data sotto forma di testo in una data in formato datetime.
TIMEVALUE (time_text) - Converte un'ora in formato testo in un'ora in formato datetime.