When we think of Power BI projects we typically think of solving business problems through creating insightful analytical reports and dashboards, Power BI is fantastic for this. However, when trying to expand your knowledge on the functionality of Power BI, I have found it incredibly useful to think outside of the scope of these types of project and explore other ways to use it. In this blog I will show an example of an unconventional Power BI project and how it helped me learn about some Power BI functionality I had not used previously.

Texas Hold ‘Em – An Example

As someone who loves playing games, I’ve always thought the idea of building a game in Power BI quite interesting. For this project I chose the popular poker game of Texas Hold ‘Em.

This project will require some special DAX functions (some of which I have not needed to use before) and Row Level Security (RLS) to work.

Overview

Implementation

1. Build the Deck:

This is a table created by entering data into Power Query, fairly straightforward:

2. Shuffle the Deck:

To shuffle the deck each card is assigned a random number using the DAX function RAND(), a function I have not used in a project prior to this. This generates a new number between 0 and 1 for each card on every refresh.

Then 13 cards must be ‘dealt’ (for the table and 4 players in this case), in this example I have utilised TOPN(), but SAMPLE() will also work great.

Passing in RandomIndex to the Order By argument in TOPN() means that we have a different deal on each refresh.

The card value and suit, which is an icon generated by the unichar code and DAX UNICHAR() function (see Joe’s frog blog here on how to implement), are added as a new column using the card value and a dimension table of suits to give a final ‘Deal’ table:

3. Deal the Cards

Each player has their own table, used for looking up the card numbers in the deal table (dealing the cards by assigning each to a player or the table).

Poker would not work if we could all see each other’s cards, this is where Row Level Security comes in.

Roles are created for each player, filtering Card Number by using DAX expressions so they can only see their own cards and the cards on the table (once revealed):

4. Reveal the Table Cards

Power BI bookmarks are very handy for changing the filters and visuals displayed on the report page with the touch of a button.

The stages of Texas Hold ‘Em have been mapped to the relevant bookmark so that we only see the correct number of cards at each stage:

5. Testing

Once published to Power BI Service, the game can be tested by selecting ‘Test as Role’ and selecting a Player role. Only the selected player’s cards should be visible:

Summary

We can see from this project that there a lot of Power BI features that have been used to implement a relatively simple game of poker, including:

  • Creating data manually in Power Query.
  • Using the RAND() DAX function to generate numbers for each row.
  • Using TOPN() to select a specified amount of rows from the top of a sorted table.
  • Using UNICHAR() to create icons, card suits in this case.
  • Creating relationships between tables (Deal to each Player table for example.).
  • Setting up Row Level Security by creating Player roles and filtering using DAX.
  • Using bookmarks to select filters for each stage of the game.
  • Testing roles in Power BI service using the ‘Test as Role’ security feature.

 

The most valuable take away from a project like this (in my opinion) is that it makes you really think about how you might implement ‘out of the ordinary’ mechanics and DAX to produce a working result. This knowledge can be carried over to future projects, where that handy Power BI feature you learned might just help.

 

 

 

Tags: , ,