DAX-funktion COLUMNSTATISTICS i LuckyTemplates

Jag skulle vilja fortsätta utforska den dynamiska användningen av DAX-funktionen COLUMNSTATISTICS() – en ny, nästan helt odokumenterad DAX-funktion som lades till i LuckyTemplates i augusti 2021 som har några mycket unika aspekter som jag tror kommer att vara mycket intressant att bekanta sig med. Du kan se hela videon av denna handledning längst ner på den här bloggen.

Jag har experimenterat mycket och lärt mig några riktigt intressanta nya saker om hur den här funktionen fungerar och vad du kan göra med den. I min första blogg om den här funktionen pratade jag om några statiska användningsområden. Idag ska jag prata om några riktigt häpnadsväckande dynamiska användningar av funktionen på sätt som inte ens IntelliSense ännu är uppdaterad om.

Innehållsförteckning

• Hur DAX-funktionen COLUMNSTATISTICS fungerar
• Slutsats

Hur DAX-funktionen COLUMNSTATISTICS fungerar

För varje tabell och fält i din datamodell producerar COLUMNSTATISTICS en tabell med sex ytterligare fält som har ett tabellnamn, kolumnnamn, min- och maxvärden för kolumnen, kardinalitet och en maxlängd. Det verkar inte som mycket data, men det finns mycket du kan göra med det.

Datan som jag använder för närvarande är bara övningsdatauppsättningen för LuckyTemplates externa verktyg. Det är en relativt enkel datamodell med femdimensionella tabeller, en faktatabell och en. En modell som denna är inte så svår att hålla reda på, men en komplex modell behöver en mycket mer komplex datauppsättningsövervakning.

Metadata för en komplex datauppsättning blir mycket viktigare när det gäller att titta på kardinalitet, vilket är antalet unika värden inom ett fält. Det har ett stort inflytande på storleken på din modell samt potentiellt bearbetningshastigheten.

Dessutom, när du bygger en modell, hjälper det att inte bara veta antalet tabeller utan även datumintervallet för dessa tabeller. När du bygger upp din Dates-tabell ser du till att täcka hela spektrumet av data i din faktatabell.

Och så, för det här exemplet, låt oss försöka lägga till en tabell till denna datamodell som jag arbetar med. I Power Query går vi till Ny källa och sedan Tom fråga . Och sedan går vi in ​​i Advanced Editor och jag ska bara klistra in en funktion som heter List.Dates . Vi kommer att använda det för att skapa en tabell över kardinaliteten på 10 000, så det är 10 000 unika datum.

Den kommer att returnera en lista med 10 000 objekt och vi kan bara konvertera den till en tabell och sedan byta namn på den tabellen.

Vi kan ändra fältet hittills. Och om vi klickar på Stäng och tillämpa, kommer vi att se att vår smarta berättelse kommer att uppdateras dynamiskt, utan att behöva uppdatera hela modellen (som vi gjorde tidigare).

Anledningen till att detta är fullt dynamiskt är att vi gör allt med åtgärder. Det intressanta är att den visar många fel, och funktionen är så ny att IntelliSense inte plockar upp den ordentligt, men den fungerar faktiskt.

Låt oss gå in i Tabular Editor 3, som jag tror är det bästa sättet att ta reda på vad de komplexa DAX-måtten faktiskt gör.

Kom ihåg att DAX-frågan returnerar tabeller snarare än skalare. Så, om vi tar denna åtgärd och kopierar över detta till en, visar det ett fel eftersom resultatet här fortfarande är en skalare. Vi kan använda felsökningsmetoden för att ersätta det RETURN-resultatet med enskilda komponenter i åtgärden.

I det här fallet kommer vi att ersätta det med ColStats , som det vi har gjort högst upp bara är att COLUMNSTATISTICStill en variabel. Med det får vi exakt vad vi förväntar oss, vilket är standardtabellen för kolumnstatistik.

Och nu vill vi bara titta på kolumnen Tabellnamn, och vi vill ta de distinkta värdena ur det och räkna dem. Det kommer att vara antalet tabeller i vår datamodell.

Först väljer vi kolumner i vår ColStats-tabell och returnerar bara det tabellnamnsfältet. I ett DAX-mått eller en DAX-fråga vill du vanligtvis returnera ett fältnamn med tabellnamnet framför sig. Men i det här fallet vet vi inte vad det lämpliga tabellnamnet refererar till eftersom det bara existerar virtuellt. Det verkar inte acceptera variabelnamnet som tabellnamn.

Så i det här fallet måste vi stanna vid det som ser ut som ett mått, men det är faktiskt en kolumnreferens utan tabellreferensen framför den. Och det fungerar fortfarande även om det är förvirrande i nomenklaturen. Om vi ​​nu ersatte den här RETURN-satsen med vår TabCol , får vi exakt vad vi förväntar oss, vilket är det modelltabellfältet.

Och sedan för resultatet, vi gör bara en räkning av de distinkta tabellkolumnerna. Så om vi ersätter detta med DISTINCT(TabsCol) får vi våra sju tabeller.

Låt oss nu ta en titt på den högsta kardinaliteten i tabellen med högsta kardinalitet och se hur vi fick dem. Detta är faktiskt ett viktigt mönster som du kan använda för många olika saker där du letar efter, inte bara maxtalet, utan maxattributet som är associerat med det numret.

Så låt oss ta en titt på MaxCardinality . Även om det här är en skalare kan vi förvandla den till en encellstabell genom att bara sätta parentes runt den. Och om vi träffar fem kommer vi att se att det är kardinalitetsvärdet på 10 000.

Så nu är frågan, hur tar vi detta och returnerar tabellen förknippad med den kardinaliteten.

För att göra det använder vi detta mycket vanliga mönster. Vi kopierar det sedan till vårt RETURN-uttalande och det kommer att ge oss raden vi förväntade oss att få, vilket är kolumnen 10 000 datum. Istället för att få en rad (eftersom det är TOPN) får vi två eftersom det är oavgjort.

Vad ColStats också gör är att det skapar, för varje tabell, ett index i en dold indexrad som heter RowNumber med det som en unik identifierare för varje rad i tabellen. Så att MaxCardinality alltid kommer att speglas av RowNumber om det är en unik identifierare.

Och så spelar det egentligen ingen roll att vi har två rader eftersom det vi tittar på är tabellnamnet Max. Och det max är det bara för att returnera något värde som annars skulle detta bara vara en naken kolumn. Men vi måste lägga lite aggregering kring det, och i det här fallet använder vi.

Om vi ​​sedan placerar MaxCardinality i vår RETURN-sats och sätter den inom parentes för att returnera en tabell istället för en skalare, returnerar den värdet på Test.

Detta TOPN DAX-mönster är ett riktigt bra mönster att komma ihåg när du i princip vill räkna ut ett max- eller min-värde och sedan returnera ett attribut som är associerat med det min eller max.

Slutsats

Jag har gett dig det allmänna tillvägagångssättet och sammanhanget som vi kan använda när det gäller att du på bästa sätt använder DAX-funktionen COLUMNSTATISTICS på ett dynamiskt sätt. Att kunna spåra detta dynamiskt i termer av en komplex modell, jag tror att det finns ett enormt värde i den här funktionen.

Ju mer vi experimenterar med COLUMNSTATISTICS-funktionen, och med dess förmåga att dynamiskt hantera metadata inom DAX, tror jag att desto mer intressanta användningsområden kommer vi att hitta. Så om du redan har hittat några intressanta användningsområden för denna DAX-funktion, vänligen meddela mig i kommentarerna nedan.

Med vänliga hälsningar!