Ak ste niekedy potrebovali dynamicky ofarbovať grafy v Power BI či v Reporting Services, tak ste si museli do merítka najprv vypočítať farbu, aby ste ju potom vedeli použiť, napr. pri podmienenom formátovaní. Väčšina nástrojov však vyžaduje zadanie farby v hexadecimálnom formáte. Preto sa teraz pozrieme na to, ako sa s tým elegantne popasovať.

V starších verziách jazyka DAX sa dala prekonvertovať číselná hodnota na hexadecimálnu hodnotu priamo cez funkciu FORMAT:

Hexa = FORMAT(číslo, “x”)

…kde stačilo do prvého parametra funkcie FORMAT zadať číslo, a druhým parametrom sme povedali, že to chceme vrátiť v hexadecimálnom formáte. V aktuálnych verziách DAX-u to už nefunguje, a potichu sa to vytratilo aj z oficiálnej dokumentácie. V starej dokumentácii to však stále je… Preto si budeme musieť pomôcť inak.

Ak to budete potrebovať použiť iba jednorazovo, tak na GitHub-e je na to už dostupné toto predvyrobené merítko:

RGB Decimal Color to Hex = 
// Manually enter components of RGB decimal color value, e.g. "RGB(1,184,170)"
VAR RedDecimalColorValue = 1
VAR GreenDecimalColorValue = 184
VAR BlueDecimalColorValue = 170
//Decimal to Hex
VAR RedPosition0Dec = MOD(RedDecimalColorValue,16)
VAR RedPosition0Div = INT(RedDecimalColorValue / 16)
VAR RedPosition1Dec = MOD(RedPosition0Div,16)
VAR RedPosition1Div = INT(RedPosition0Div / 16)
VAR GreenPosition0Dec = MOD(GreenDecimalColorValue,16)
VAR GreenPosition0Div = INT(GreenDecimalColorValue / 16)
VAR GreenPosition1Dec = MOD(GreenPosition0Div,16)
VAR GreenPosition1Div = INT(GreenPosition0Div / 16)
VAR BluePosition0Dec = MOD(BlueDecimalColorValue,16)
VAR BluePosition0Div = INT(BlueDecimalColorValue / 16)
VAR BluePosition1Dec = MOD(BluePosition0Div,16)
VAR BluePosition1Div = INT(BluePosition0Div / 16)
VAR RedPosition0Hex = SWITCH(RedPosition0Dec,10,"A",11,"B",12,"C",13,"D",14,"E",15,"F",RedPosition0Dec)
VAR RedPosition1Hex = SWITCH(RedPosition1Dec,10,"A",11,"B",12,"C",13,"D",14,"E",15,"F",RedPosition1Dec)
VAR GreenPosition0Hex = SWITCH(GreenPosition0Dec,10,"A",11,"B",12,"C",13,"D",14,"E",15,"F",GreenPosition0Dec)
VAR GreenPosition1Hex = SWITCH(GreenPosition1Dec,10,"A",11,"B",12,"C",13,"D",14,"E",15,"F",GreenPosition1Dec)
VAR BluePosition0Hex = SWITCH(BluePosition0Dec,10,"A",11,"B",12,"C",13,"D",14,"E",15,"F",BluePosition0Dec)
VAR BluePosition1Hex = SWITCH(BluePosition1Dec,10,"A",11,"B",12,"C",13,"D",14,"E",15,"F",BluePosition1Dec)
VAR RGBDecimalColorAsHex = "#" & RedPosition1Hex & RedPosition0Hex & GreenPosition1Hex & GreenPosition0Hex & BluePosition1Hex & BluePosition0Hex
RETURN RGBDecimalColorAsHex

Ako vidíte, tak sa tu poctivo počíta každá jedna zložka. A bez podpory premenných by to bola celkom pakáreň.

Opakované použitie

Čo však, ak to potrebujete použiť viackrát? Keďže DAX zatiaľ nemá možnosť vytvárať si vlastné funkcie, tak to budeme musieť spraviť ešte inak. A ako inak, aj teraz si pomôžeme fintou cez Power Query. Jazyk M má totižto funkciu Number.ToText, ktorá vie konvertovať číslo do hexadecimálneho formátu takto:

Number.ToText(číslo, “x”)

Čiže podobne, ako to mala niekedy funkcia FORMAT v jazyku DAX. A pýtate sa, ako to dostaneme do merítka? Vytvoríme si v Power Query sekvenčnú tabuľku, kde budeme mať všetky možné číselné hodnoty v jednom stĺpci, a ich hexadecimálny formát v druhom stĺpci. Takúto tabuľku si potom naimportujeme do dátového modelu, a v jazyku DAX si svoju farbu vyhľadáme v tejto tabuľke pomocou funkcie LOOKUPVALUE.

V prípade farieb v počítači sú všetky farby zadávané v RGB formáte #XXXXXX, čiže ako 6-miestne hexadecimálne číslo. To je dokopy 2^24, resp. 16 777 216 hodnôt. Opäť niečo, čo síce klasický Excel nezvládne, ale Power BI, resp. Power Query s PowerPivotom v Exceli, to zvládnu za pár sekúnd. Takže poďme na to.

Najprv si teda vytvoríme sekvenčnú tabuľku v Power Query, ako nový prázdny dotaz, týmto vzorcom:

= List.Generate(() => 0, each _ < 16777216, each _ + 1)

To vytvorí zoznam s hodnotami od 0 do 16777215:

Následne to rovnako ako v článku o sekvenčných tabuľkách, prekonvertujeme na zoznam, v hlavnom menu na poslednej záložke Tranformovať, kliknutím na tlačítko “Do tabuľky”:

Potom si ten stĺpec premenujte na názov Hodnota.

Potom pridáme vlastný vypočítaný stĺpec, do ktorého si vypočítame hexadecimálnu hodnotu pre každý riadok. Kliknite teda v hlavnom menu na záložku “Pridať stĺpec”, a tam na tlačítko “Vlastný stĺpec”:

Tam zadajte názov stĺpca “Hex hodnota”, a tento vzorec:

= “#” & Text.Upper(Text.PadStart(Number.ToText([Hodnota], “x”), 6, “0”))

Malo by vám to pridať takýto stĺpec:

Použili sme tento postup:

1. funkciou Number.ToText sme prekonvertovali číselnú hodnotu na hexadecimálnu hodnotu,
2. funkciou Text.PadStart sme doplnili hexadecimálnu hodnotu na 6 miest nulami zľava,
3. funkciou Text.Upper sme zmenili všetky písmená na veľké písmená,
4. nakoniec sme k tomu prilepili znak mriežky, ktorý sa používa v reportovacích nástrojoch, aj v Power BI, na indikovanie hexadecimálneho kódu farby.

Takúto tabuľku už potom stačí iba premenovať – v našom prípade na názov Dec2Hex – a naimportovať do dátového modelu, stlačením tlačítka “Zavrieť a načítať”, resp. “Zavrieť a použiť”, na záložke “Domov”.

Takáto tabuľka však nebude veľmi malá, čo sa týka miesta v pamäti. Podľa Power BI Analyzera bude zaberať okolo 1 GB miesta v pamäti:

A po uložení na disk to zväčší súbor o cca. 500 MB. To by v dnešnej dobe nemalo vadiť. Ak by to bol problém, tak sa na konci tohto článku pozrite na pamäťovú optimalizáciu tejto techniky.

Keď už to teda máme v dátovom modeli, tak sa pozrieme, ako to použijeme v jazyku DAX.

Povedzme, že sme mali predtým takéto merítko, do ktorého sme si vypočítali nejakým vzorcom číslo farby. Ja teraz použijem pre ilustráciu natvrdo číslo 12345:

Farba = 12345

A túto farbu teraz prekonvertujeme do hexadecimálneho formátu klasickým vyhľadaním v tabuľke, ktorú sme si vyrobili vyššie:

Farba = LOOKUPVALUE(Dec2Hex[Hex hodnota]; Dec2Hex[Hodnota]; 12345)

Použili sme funkciu LOOKUPVALUE, ktorá sa používa rovnako ako základné použitie excelovskej funkcie VLOOKUP. Posledný parameter je číslo, ktoré hľadáte. V našom konkrétnom prípade to hodnotu 12345 “prekonvertuje” na hexadecimálnu hodnotu #003039:

A to je celý trik 🙂 A aj keď DAX už na to nemá žiadnu funkciu, tak takto sme si naemulovali preklad z obyčajnej hodnoty na hexadecimálnu hodnotu.

Ako to zoptimalizovať

A čo teraz v prípade, že buď máte málo RAM-ky, alebo nechcete, aby bol ten súbor pol gigabajtový len kvôli tomuto triku? Napr. aby ste neplytvali miestom v cloude či na serveri? Vtedy na to budeme musieť ísť o chlp inak.

Začneme rovnako ako v predchádzajúcom prípade. Ale v tomto prípade dáme nagenerovať tú tabuľku iba pre hodnoty od 0 do 255, týmto vzorcom:

= List.Generate(() => 0, each _ < 256, each _ + 1)

Potom to opäť prekonvertujeme na tabuľku, a spravíme vlastný vypočítaný stĺpec, bez mriežky na začiatku, a doplnený nulami iba na 2 miesta zľava. Stĺpec sa opäť bude volať “Hex hodnota”, a bude mať tento vzorec:

= Text.Upper(Text.PadStart(Number.ToText([Hodnota], “x”), 2, “0”))

Tabuľku opäť pomenujeme ako “Dec2Hex”, a naimportujeme do dátového modelu.

Potom v dátovom modeli vytvoríme opäť merítko Farba, v ktorom ste si už akože vypočítali svoju farbu ako normálne číslo. To uložíme do premennej na začiatku vzorca. Namiesto toho čísla si tam samozrejme dajte svoj daxový vzorec, ktorým počítate farbu. Ja tam teda opäť zadám natvrdo číslo 12345, a vzorec pre to merítko bude takýto:

Farba =
VAR hodnota = 12345
RETURN
“#” & LOOKUPVALUE(Dec2Hex[Hex hodnota]; Dec2Hex[Hodnota]; QUOTIENT(hodnota; 65536)) & LOOKUPVALUE(Dec2Hex[Hex hodnota]; Dec2Hex[Hodnota]; MOD(QUOTIENT(hodnota; 256); 256)) & LOOKUPVALUE(Dec2Hex[Hex hodnota]; Dec2Hex[Hodnota]; MOD(hodnota; 256))

…alebo vypeknené cez DAX Formatter:

Výsledok bude rovnaký ako v predchádzajúcom prípade:

Celý trik bol v tom, že sme si matematicky “rozložili” to pôvodné číslo na jeho 3 rovnako veľké kusy po 8 bitoch, ktoré sme potom po jednom našli funkciou LOOKUPVALUE v našej tabuľke hexadecimálnych kódov z Power Query. Použili sme pri tom tieto 2 funkcie:

1. MOD – vráti zvyšok po celočíselnom delení – inde známe ako operácia “modulo”,
2. QUOTIENT – vráti výsledok po celočíselnom delení. Alternatívne viete použiť aj funkcie ako INT či FLOOR.

A spotreba pamäte? Podľa Power BI Analyzera iba 1 MB:

…a po reštarte Power BI Desktopu ešte menej – iba 24 kB. Výsledný súbor to zväčšilo iba o 32 kB. A to sa už oplatí 🙂 Síce je za tým trochu komplikovanejší vzorec, ale ak si to pomenujete ako to mám ja, tak vám stačí zmeniť hodnotu/vzorec len v tej premennej na začiatku. To sa dá celkom prežiť. Najmä keď si zoberiete pamäťové nároky tohto riešenia, veľkosť súboru, a aj úspory vo vašom konte v cloude.

Takže takto sa to dá spraviť celkom jednoducho. A najmä, ide to spraviť, aj keď tam nie je pre to spravená podpora. Pretože všetko ide spraviť, a väčšinou aj ľahko, ak už viete ako na to 🙂