I thought it might be fun to try something a little different with this blog and use a combination of bookmarks with a little DAX magic to come up with a game that plays a hand of Blackjack. Power BI and DAX are not the ideal engine to build a game of Blackjack with, but it was an interesting challenge and I thought I would share my approach that takes advantage of quite a few novel techniques.
Download the PBIX file here: https://1drv.ms/u/s!AtDlC2rep7a-pD4xXIvpHFLfHziw
First of all, let’s look at the finished result. The PBIX file allows you to play a single hand that involves you playing the House. The first view of the table shows the first card for both you and the house. You can then click the hit button to deal new cards until you decide to hold (or go bust), at which point the house cards are revealed with a dynamic message that shows if you won or lost. There is no mechanism to bet or play additional hands. The game is dynamic enough to understand if you win, lose or draw based on your decision on when to hold.
Data Model
The data model for the game consists of 6 unrelated data-tables. Three of the tables control the side of the game for Player 1 (you), while the other three data-tables control the game for the house. In the diagram below, the three data tables on the left-hand side (‘P1’, ‘Player 1’ and ‘P1 Turn’) control the game for Player 1, while the three data tables on the left control the game for the House.
The P1 and P2 data-tables at the top are effectively the shuffled cards as dealt in order of ‘Turn’. They both have the same pattern and structure with 10 rows and three columns. The reason there are two tables is so we can have a random set of cards for Player 1 (the P1 data-table) and a different set of random cards for the House (P2).
The ‘Turn’ column in each data-table is numbered 1 to 10 and controls the order of the deal. The ‘Cards’ column contains a random number between 1 and 13 (1 = Ace, 2 = 2 … 11 = J, 12 = Q, 13 = K), while the ‘Suit’ column is a random number between 1 and 4 to allow us to deal cards from any of the four suits (♠ ♣ ♥ ♦). These tables are generated in Power Query using a random number technique to ensure that different values were generated for each row of both tables.
My first stab at creating a random number in Power Query ended up providing the same number for each row, so I passed the value of the previous step as a way to force Power Query to generate a new value for each row
The M code for the P1 table starts by generating a list of numbers {1..10} which is then converted to a table with two extra columns added containing the random numbers needed for Card (1 to 13) and Suit (1 to 4).
let
Source = {1..10},
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
AddedCustom1 = Table.AddColumn( #"Converted to Table", "Custom.1", each List.Random(1)),
ExpandedCustom.2 = Table.ExpandListColumn(AddedCustom1, "Custom.1"),
#"Multiplied Column" = Table.TransformColumns(ExpandedCustom.2, {{"Custom.1", each _ * 13, type number}}),
#"Rounded Up" = Table.TransformColumns(#"Multiplied Column",{{"Custom.1", Number.RoundUp, Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Rounded Up",{{"Column1", "Turn"}, {"Custom.1", "Cards"}}),
AddedCustom2 = Table.AddColumn( #"Renamed Columns", "Custom.2", each List.Random(1)),
ExpandedCustom.3 = Table.ExpandListColumn(AddedCustom2, "Custom.2"),
#"Multiplied Column1" = Table.TransformColumns(ExpandedCustom.3, {{"Custom.2", each _ * 3, type number}}),
#"Rounded Up1" = Table.TransformColumns(#"Multiplied Column1",{{"Custom.2", Number.RoundUp, Int64.Type}}),
#"Renamed Columns1" = Table.RenameColumns(#"Rounded Up1",{{"Custom.2", "Suit"}})
in
#"Renamed Columns1"
The M code for the P2 table
let
Source = P1
in
Source
While the P2 table is a copy of P1, it generates a different set of random numbers.
The P1 and P2 tables are hidden from the final model and are used as the basis for the next two tables.
The DAX fun begins in the next data-table.
For Player 1, we generate a data-table based on the P1 table from the previous step. There will be 1-row per turn and a series of columns that help determine if the player wins the game.
Player 1 =
VAR AddCards =
ADDCOLUMNS(
P1,
"Card Value",IF('P1'[Cards]>10,10,'P1'[Cards]) ,
"Card Face",UNICHAR(127136 + 'P1'[Cards] + ('P1'[Suit]*16) -16),
"Card Colour",IF('P1'[Suit] IN {1,4},1,0)
)
VAR AddScore = ADDCOLUMNS(
AddCards,
"Score",
VAR CardsSoFar = FILTER(AddCards,[Turn]<=EARLIER([Turn]))
VAR HasAceCard = IF(COUNTROWS(FILTER(CardsSoFar,[Cards]=1))>0,10,0)
VAR B1 =SUMX(CardsSoFar,[Card Value])
VAR B2 =SUMX(CardsSoFar,[Card Value]) + HasAceCard
RETURN
SWITCH(
TRUE(),
B1 > 21 && B1 > 21 , 99 ,
B2 <= 21 , B2 ,
B1 <= 21 , B1 ,
99 -- 99 = Bust
)
)
RETURN AddScore
The Turn, Cards and Suit columns are derived from the P1 table unchanged, but four new columns are added. No rows are added or filtered.
Player 1 =
VAR AddCards =
ADDCOLUMNS(
P1,
"Card Value",IF('P1'[Cards]>10,10,'P1'[Cards]) ,
"Card Face",UNICHAR(127136 + 'P1'[Cards] + ('P1'[Suit]*16) -16),
"Card Colour",IF('P1'[Suit] IN {1,4},1,0)
)
Firstly the ‘Card Value’ column converts the ‘Cards’ column back to the numeric value associated with the card. Ace = 1, 2 = 2…. J, Q & K all equal 10. This value is used to determine your score to compare in the game. While an Ace card can represent two possible values (1 or 11), this is handled later in the code that generates the ‘Score’ column.
Player 1 =
VAR AddCards =
ADDCOLUMNS(
P1,
"Card Value",IF('P1'[Cards]>10,10,'P1'[Cards]) ,
"Card Face",UNICHAR(127136 + 'P1'[Cards] + ('P1'[Suit]*16) -16),
"Card Colour",IF('P1'[Suit] IN {1,4},1,0)
)
The ‘Card Face’ column uses the UNICHAR function to combine both the ‘Cards’ and ‘Suit’ columns to a Unicode value that represents one of the 52 playing cards.
Player 1 =
VAR AddCards =
ADDCOLUMNS(
P1,
"Card Value",IF('P1'[Cards]>10,10,'P1'[Cards]) ,
"Card Face",UNICHAR(127136 + 'P1'[Cards] + ('P1'[Suit]*16) -16),
"Card Colour",IF('P1'[Suit] IN {1,4},1,0)
)
Then a ‘Card Colour’ column is generated to return a 1 or 0 that will be used by conditional formatting to set the font to be red for heart and diamond suits, and black for club and spade suits.
VAR AddScore = ADDCOLUMNS(
AddCards,
"Score",
VAR CardsSoFar = FILTER(AddCards,[Turn]<=EARLIER([Turn]))
VAR HasAceCard = IF(COUNTROWS(FILTER(CardsSoFar,[Cards]=1))>0,10,0)
VAR B1 =SUMX(CardsSoFar,[Card Value])
VAR B2 =SUMX(CardsSoFar,[Card Value]) + HasAceCard
RETURN
SWITCH(
TRUE(),
B1 > 21 && B1 > 21 , 99 ,
B2 <= 21 , B2 ,
B1 <= 21 , B1 ,
99 -- 99 = Bust
)
)
Finally, a ‘Score’ column is added that performs a cumulative sum over the ‘Card Value’ column based on incrementing turns. A nested variable scope is introduced to handle the scenario whereby an Ace card can have a numeric value of either 1 or 11. The logic first tests to see if there has been an Ace in any of the previous turns and counts the Aces so far. The B1 and B2 variables then provide the two possible best scores based on the combination of Aces dealt to this point. The SWITCH statement analyses both values stored in the B1 and B2 variables to determine the best possible score under 21 to return. If both B1 and B2 are over 21, then a value of 99 is returned which represents a bust.
The last of the three data-tables in the model used to control Player 1 is the ‘P1 Turn’ data-table. This is simply a 10-row single column table with numbers 1 through 10. The purpose of this table is to use in conjunction with a slicer that will help keep track of what turn Player 1 is up to. A series of 5 bookmarks will be used to snapshot a slicer selected in 5 different states. A series of bookmarks will be taken with this slicer having a different value selected which is how the game can keep track of the progress through the game.
A [P1 Card Filter] calculated measure keeps track of the selected value over the above slicer which is used as a filter on the table-visual that is used to reveal cards for Player 1.
P1 Card Filter =
IF(
MAX('Player 1'[Turn]) <=SELECTEDVALUE('P1 Turn'[Value]),
1,
0
)
A [P1 Score] calculated measure uses the [P1 Card Fitler] measure to determine the current score for Player 1 depending on how many cards they have had dealt.
P1 Score =
VAR varScore =
MAXX(
FILTER(
'Player 1',
[P1 Card Filter]=1
),
[Score])
RETURN
IF(
varScore>21,
"Bust",
varScore
)
Both the [P1 Card Filter] and [P1 Score] calculated measures have equivalent versions to handle the scoring for the House, although are slightly different in that the house only has 1 hand, or shows their final state.
The DAX to generate the data-table to control the scoring for the house is as follows
House =
VAR AddCards =
ADDCOLUMNS(
P2,
"Card Value",IF('P2'[Cards]>10,10,'P2'[Cards]) ,
"Card Face",UNICHAR(127136 + 'P2'[Cards] + ('P2'[Suit]*16) -16),
"Card Colour",IF('P2'[Suit] IN {1,4},1,0)
)
VAR AddScore = ADDCOLUMNS(
AddCards,
"Score",
VAR CardsSoFar = FILTER(AddCards,[Turn]<=EARLIER([Turn]))
VAR HasAceCard = IF(COUNTROWS(FILTER(CardsSoFar,[Cards]=1))>0,10,0)
VAR B1 =SUMX(CardsSoFar,[Card Value])
VAR B2 =SUMX(CardsSoFar,[Card Value]) + HasAceCard
RETURN
SWITCH(
TRUE(),
B1 > 21 && B1 > 21 , 99 ,
B2 <= 21 , B2 ,
B1 <= 21 , B1 ,
99 -- 99 = Bust
)
)
VAR HouseHitORHold = ADDCOLUMNS(AddScore,"Hit or Hold" , IF([Score]<17,"Hit","Hold") )
RETURN FILTER(HouseHitORHold,[Turn]<=MINX(FILTER(HouseHitORHold,[Hit or Hold]="Hold"),[Turn]))
This table is similar to the ‘Player 1’ table in that it carries forward the three columns (Turn, Cards & Score) from the ‘P2’ table generated in Power Query. The difference is that a new column called ‘Hit or Hold’ is added to control the rule that says the House must keep dealing until they hit 17. Once the cumulative ‘Score’ is at 17 or greater the house will stop dealing.
The final RETURN statement filters the overall data-table so it only has as many rows as the first hold – which could be a bust score.
The cards can only be in 2 states for the House. State 1 is that only the first card is showing, while the other state is that all cards are showing.
Report View
There are two report pages. The first is simply a welcome screen with a button linked to a bookmark that takes you to the second report page where the game takes place.
- A standard bookmark button that is linked to an action that jumps back to a bookmark showing the initial welcome screen.
- There are five identical images added that link to slightly different bookmarks. Each image sets a different value on the slicers at item (10) and then hides itself to reveal the next image. The [P1 Card Fitler] calculated measure watches the slicer at (10) and is used as a filter on the data-table at (5) to control how many cards to show.
- A single image that is linked to a final bookmark that reveals the house cards AND the [result] measure to show at that point, if Player 1 or the House has the best score.
- A simple card visual that shows the current score for Player 1 that is based on how many cards have been dealt
- A table visual with two columns to show the cards for Player 1. The first column has the Turn number. This is hidden by making the font colour the same as the background. The second column shows the UNICHAR card and has conditional formatting to set the font to red or black depending on the value for ‘Card Colour’. Finally, this table has uses the [P1 Card Filter] calculated measure to determine how many rows to display which is based on the slicer at (10)
- A table visual with two columns two show the cards for the House. The first column is hidden by making the font colour the same as the background. The second column initially shows only one card. When the Hold image is clicked (3), all the cards for the House are revealed showing the pre-determined values. Conditional formatting is applied to control the colour of the cards to match the suit and the table visual uses the [House Card Filter] calculated measure to control which cards should be shown based on the slicer at (9).
- A simple card visual that keeps track of the score for the house based on which cards have been revealed.
- A card visual that shows the result. The result will be dynamic and will show if Player 1 has won, lost or drawn.
- A slicer to control the turn number for the House. This slicer is hidden at all times and is initially set to value 1. The final bookmark sets this slicer to 2 which causes all the House cards to be shown.
- A slicer to control the turn number for Player 1. This slicer is also hidden at all times and is stepped through from value 1 through to 5 by each of the images at (2).
The game is dynamic enough that with the same set of cards, depending on how many times the Player hits and then holds, they can win, lose or draw.
There is no concept of betting and it’s only good enough for one hand. It does showcase a variety of features available in Power BI. The refresh button needs to be clicked to generate a new set of cards using the randomiser in Power Query. Unfortunately the “Publish to Web” feature offers no ability to randomise the cards, so you end up playing the same hand over and over.
Download the PBIX file here:
https://1drv.ms/u/s!AtDlC2rep7a-pD4xXIvpHFLfHziw
Try the game out using Publish to Web (the Unicode feature doesn’t seem to render the cards)/
Totally awesome ! Love it. Congrats
Wow, wow, wow! Brilliant!
So many important techniques exhibited in a fun way.