Translytical Sudoku

Microsoft’s recent announcement of Translytical Task Flows for Power BI has opened up a world of practical applications. But for me, the first thought was how can I use this to do something impractical and build a game? I previously inspired by Phil Seamark impressive collection Power BI games, especially his innovative Sudoku implementation that maintained game state using slicers! With the full power of Microsoft Fabric and a backend database, we can now truly manage game state, making the game simpler to build and play, whilst also making it more feature rich.

Understanding Translytical Task Flows

Translytical Task Flows, while still in preview, are a game-changer for Power BI users. To enable them, you’ll need to go to Options > Preview features > Translytical Task Flows in Power BI Desktop.

At their core, they allow buttons within your Power BI reports to trigger Fabric User Data Functions (UDF). UDFs are python function that can perform an action, such as initiate changes in a Fabric SQL database. Within the button definition you map a button, list, or text slicer; a data field; or measures from the report to the input parameters of the UDF.

Design Considerations

Sudoku is a logic puzzle played on a 9x9 grid. Your goal is to fill the grid with digits from 1 to 9, ensuring that each column, each row, and each of the nine 3x3 subgrids contains all the digits from 1 to 9.

Our approach leverages a Fabric SQL database to store the game’s initial state and, crucially, the player’s progress. Players will interact with the Power BI report by selecting a cell on the grid and a candidate value. Pressing a button will then activate a UDF, which in turn updates the database.

A key feature for serious Sudoku players is pencil marks. These small numbers in the corners of a cell represent all possible valid digits for that cell. Our game will support this by allowing players to toggle pencil marks using a dedicated button.

Considering that multiple users might access the report, we’ll implement Row Level Security (RLS) using USERPRINCIPALNAME() to ensure each player can only access their own games.

Here’s an architectural overview of how all these components fit together:

Creating The SQL Database

The first step is to set up our Fabric SQL database with the necessary tables to manage game data:

• PuzzleTemplates: Stores predefined Sudoku puzzles
• PuzzleInitialState: Records the starting values for each puzzle template
• Games: Tracks individual game instances initiated by players
• GameState: Holds the current values entered by players for a specific game
• PencilMarks: Manages the pencil marks added by players

CREATE TABLE PuzzleTemplates (
    PuzzleId INT PRIMARY KEY IDENTITY(1,1),
    PuzzleName NVARCHAR(100),
    Difficulty NVARCHAR(20),
    CreatedDate DATETIME2 DEFAULT GETDATE()
);

CREATE TABLE PuzzleInitialState (
    PuzzleId INT,
    Position INT CHECK (Position BETWEEN 1 AND 81),
    InitialValue INT CHECK (InitialValue BETWEEN 1 AND 9),
    PRIMARY KEY (PuzzleId, Position),
    FOREIGN KEY (PuzzleId) REFERENCES PuzzleTemplates(PuzzleId)
);

CREATE TABLE Games (
    GameId INT PRIMARY KEY IDENTITY(1,1),
    PuzzleId INT,
    GameName NVARCHAR(100),
    CreatedDate DATETIME2 DEFAULT GETDATE(),
    LastModifiedDate DATETIME2 DEFAULT GETDATE(),
    FOREIGN KEY (PuzzleId) REFERENCES PuzzleTemplates(PuzzleId)
);

CREATE TABLE GameState (
    GameId INT,
    Position INT CHECK (Position BETWEEN 1 AND 81),
    CurrentValue INT CHECK (CurrentValue BETWEEN 1 AND 9),
    IsInitialValue BIT DEFAULT 0,
    LastModifiedDate DATETIME2 DEFAULT GETDATE(),
    PRIMARY KEY (GameId, Position),
    FOREIGN KEY (GameId) REFERENCES Games(GameId)
);

CREATE TABLE PencilMarks (
    GameId INT,
    Position INT CHECK (Position BETWEEN 1 AND 81),
    PencilValue INT CHECK (PencilValue BETWEEN 1 AND 9),
    IsActive BIT DEFAULT 1,
    CreatedDate DATETIME2 DEFAULT GETDATE(),
    LastModifiedDate DATETIME2 DEFAULT GETDATE(),
    PRIMARY KEY (GameId, Position, PencilValue),
    FOREIGN KEY (GameId) REFERENCES Games(GameId)
);

To efficiently populate our Sudoku grid in Power BI via Direct Query, we’ll create a view that combines all relevant game state information:

CREATE VIEW vw_GameState AS
SELECT
    g.GameId,
    g.GameName,
    g.PuzzleId,
    coord.Position,
    gs.CurrentValue,
    gs.IsInitialValue,
    STRING_AGG(CAST(pm.PencilValue AS VARCHAR), '') AS PencilMarks
FROM Games g
CROSS JOIN (
    -- Generate all possible positions (1-81)
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Position
    FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9)) X(n)
    CROSS JOIN (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9)) Y(n)
) coord
LEFT JOIN GameState gs ON g.GameId = gs.GameId 
    AND coord.Position = gs.Position
LEFT JOIN PencilMarks pm ON g.GameId = pm.GameId 
    AND coord.Position = pm.Position
    AND pm.IsActive = 1
GROUP BY g.GameId, g.GameName, g.PuzzleId,
         coord.Position, gs.CurrentValue, gs.IsInitialValue;

We’ll use stored procedures to encapsulate the core game actions, making them callable from our UDFs:

• sp_InitializeGame: Creates a new instance of a puzzle for a player
• sp_SetCellValue: Adds or removes a selected digit from a selected cell
• sp_TogglePencilMark Adds or removes a pencil mark for a selected cell
• sp_ClearUserEntries Resets all user-entered digits and pencil marks for the current game

CREATE PROCEDURE sp_InitializeGame
    @GameName NVARCHAR(100),
    @PuzzleId INT
AS
BEGIN
    DECLARE @GameId INT;
    
    -- Check if game with same name and puzzle already exists
    IF EXISTS (SELECT 1 FROM Games WHERE GameName = @GameName AND PuzzleId = @PuzzleId)
    BEGIN
        RAISERROR('A game with this name already exists for this puzzle.', 16, 1);
        RETURN;
    END;

    -- Create new game
    INSERT INTO Games (GameName, PuzzleId)
    VALUES (@GameName, @PuzzleId);
    
    SET @GameId = SCOPE_IDENTITY();
    
    -- Copy initial state from puzzle template
    INSERT INTO GameState (GameId, Position, CurrentValue, IsInitialValue)
    SELECT @GameId, pis.Position, pis.InitialValue, 1
    FROM PuzzleInitialState pis
    WHERE pis.PuzzleId = @PuzzleId;
    
    SELECT @GameId AS GameId;
END;
GO

CREATE PROCEDURE sp_SetCellValue
    @GameId INT,
    @Position INT,
    @Value INT
AS
BEGIN
    -- Check if cell is an initial value (cannot be changed)
    IF EXISTS (SELECT 1 FROM GameState 
               WHERE GameId = @GameId AND Position = @Position AND IsInitialValue = 1)
    BEGIN
        RAISERROR('Cannot modify initial puzzle values.', 16, 1);
        RETURN;
    END;
    
    -- Check if trying to set the same value that's already there (toggle off)
    IF EXISTS (SELECT 1 FROM GameState 
               WHERE GameId = @GameId AND Position = @Position 
               AND CurrentValue = @Value AND IsInitialValue = 0)
    BEGIN
        -- Remove the value (toggle off)
        DELETE FROM GameState 
        WHERE GameId = @GameId AND Position = @Position;
        
        -- Clear pencil marks for this cell
        DELETE FROM PencilMarks 
        WHERE GameId = @GameId AND Position = @Position;
        
        RETURN;
    END;
    
    -- Insert or update the cell value
    MERGE GameState AS target
    USING (VALUES (@GameId, @Position, @Value)) AS source (GameId, Position, CurrentValue)
    ON target.GameId = source.GameId 
       AND target.Position = source.Position
    WHEN MATCHED THEN
        UPDATE SET CurrentValue = source.CurrentValue, LastModifiedDate = GETDATE()
    WHEN NOT MATCHED THEN
        INSERT (GameId, Position, CurrentValue, IsInitialValue)
        VALUES (source.GameId, source.Position, source.CurrentValue, 0);
    
    -- Clear pencil marks for this cell
    DELETE FROM PencilMarks 
    WHERE GameId = @GameId AND Position = @Position;
END;
GO

CREATE PROCEDURE sp_TogglePencilMark
    @GameId INT,
    @Position INT,
    @PencilValue INT
AS
BEGIN
    -- Only allow pencil marks if cell is empty
    IF EXISTS (SELECT 1 FROM GameState 
               WHERE GameId = @GameId AND Position = @Position AND CurrentValue IS NOT NULL)
    BEGIN
        RAISERROR('Cannot add pencil marks to filled cells.', 16, 1);
        RETURN;
    END;
    
    -- Toggle the pencil mark
    IF EXISTS (SELECT 1 FROM PencilMarks 
               WHERE GameId = @GameId AND Position = @Position 
               AND PencilValue = @PencilValue AND IsActive = 1)
    BEGIN
        -- Remove the pencil mark
        UPDATE PencilMarks 
        SET IsActive = 0, LastModifiedDate = GETDATE()
        WHERE GameId = @GameId AND Position = @Position 
              AND PencilValue = @PencilValue;
    END
    ELSE
    BEGIN
        -- Add or reactivate the pencil mark
        MERGE PencilMarks AS target
        USING (VALUES (@GameId, @Position, @PencilValue)) AS source (GameId, Position, PencilValue)
        ON target.GameId = source.GameId 
           AND target.Position = source.Position
           AND target.PencilValue = source.PencilValue
        WHEN MATCHED THEN
            UPDATE SET IsActive = 1, LastModifiedDate = GETDATE()
        WHEN NOT MATCHED THEN
            INSERT (GameId, Position, PencilValue, IsActive)
            VALUES (source.GameId, source.Position, source.PencilValue, 1);
    END;
END;
GO

CREATE PROCEDURE sp_ClearUserEntries
    @GameId INT
AS
BEGIN
    -- Delete all user-entered values (keep only initial values)
    DELETE FROM GameState 
    WHERE GameId = @GameId AND IsInitialValue = 0;
    
    -- Clear all pencil marks
    DELETE FROM PencilMarks 
    WHERE GameId = @GameId;
    
    -- Update game's last modified date
    UPDATE Games 
    SET LastModifiedDate = GETDATE()
    WHERE GameId = @GameId;
END;
GO

Finally, let’s add some sample Sudoku puzzles to our PuzzleTemplates and PuzzleInitialState tables:

INSERT INTO PuzzleTemplates (PuzzleName, Difficulty) VALUES 
('Sample Easy Puzzle', 'Easy'),
('Sample Medium Puzzle', 'Medium'),
('Sample Hard Puzzle', 'Hard'),
('Sample Expert Puzzle', 'Expert');

-- EASY PUZZLE (PuzzleId = 1) - converted to positions
-- Position mapping: Position = (Row-1) * 9 + Column
-- Row 1: positions 1-9, Row 2: positions 10-18, etc.
INSERT INTO PuzzleInitialState (PuzzleId, Position, InitialValue) VALUES
-- Row 1: 5 3 . | . 7 . | . . .
(1, 1, 5), (1, 2, 3), (1, 5, 7),
-- Row 2: 6 . . | 1 9 5 | . . .
(1, 10, 6), (1, 13, 1), (1, 14, 9), (1, 15, 5),
-- Row 3: . 9 8 | . . . | . 6 .
(1, 20, 9), (1, 21, 8), (1, 26, 6),
-- Row 4: 8 . . | . 6 . | . . 3
(1, 28, 8), (1, 32, 6), (1, 36, 3),
-- Row 5: 4 . . | 8 . 3 | . . 1
(1, 37, 4), (1, 40, 8), (1, 42, 3), (1, 45, 1),
-- Row 6: 7 . . | . 2 . | . . 6
(1, 46, 7), (1, 50, 2), (1, 54, 6),
-- Row 7: . 6 . | . . . | 2 8 .
(1, 56, 6), (1, 61, 2), (1, 62, 8),
-- Row 8: . . . | 4 1 9 | . . 5
(1, 67, 4), (1, 68, 1), (1, 69, 9), (1, 72, 5),
-- Row 9: . . . | . 8 . | . 7 9
(1, 77, 8), (1, 80, 7), (1, 81, 9);

-- MEDIUM PUZZLE (PuzzleId = 2) - converted to positions
INSERT INTO PuzzleInitialState (PuzzleId, Position, InitialValue) VALUES
-- Row 1: . . . | 6 . . | . . 3
(2, 4, 6), (2, 9, 3),
-- Row 2: . 7 . | . 9 . | 2 . .
(2, 11, 7), (2, 14, 9), (2, 16, 2),
-- Row 3: 5 . . | . . . | . . .
(2, 19, 5),
-- Row 4: . . . | . . . | . 1 .
(2, 35, 1),
-- Row 5: . . 1 | . 8 . | 4 . .
(2, 39, 1), (2, 41, 8), (2, 43, 4),
-- Row 6: . 6 . | . . . | . . .
(2, 47, 6),
-- Row 7: . . . | . . . | . . 7
(2, 63, 7),
-- Row 8: . . 4 | . 2 . | . 9 .
(2, 66, 4), (2, 68, 2), (2, 71, 9),
-- Row 9: 9 . . | . . 8 | . . .
(2, 73, 9), (2, 78, 8);

-- HARD PUZZLE (PuzzleId = 3) - converted to positions
INSERT INTO PuzzleInitialState (PuzzleId, Position, InitialValue) VALUES
-- Row 1: . . . | . . . | . 1 .
(3, 8, 1),
-- Row 2: 4 . . | . . . | . . .
(3, 10, 4),
-- Row 3: . . . | . . . | 6 . 2
(3, 25, 6), (3, 27, 2),
-- Row 4: . . . | . . . | . 7 .
(3, 35, 7),
-- Row 5: . 8 . | . . . | . 4 .
(3, 38, 8), (3, 44, 4),
-- Row 6: . 3 . | . . . | . . .
(3, 47, 3),
-- Row 7: 7 . 9 | . . . | . . .
(3, 55, 7), (3, 57, 9),
-- Row 8: . . . | . . . | . . 5
(3, 72, 5),
-- Row 9: . 2 . | . . . | . . .
(3, 74, 2);

-- EXPERT PUZZLE (PuzzleId = 4) - converted to positions
INSERT INTO PuzzleInitialState (PuzzleId, Position, InitialValue) VALUES
-- Row 1: . . . | . . . | . . 9
(4, 9, 9),
-- Row 2: . . . | . . 3 | . 8 .
(4, 15, 3), (4, 17, 8),
-- Row 3: . . 1 | . 2 . | . . .
(4, 21, 1), (4, 23, 2),
-- Row 4: . . . | . . . | . . 2
(4, 36, 2),
-- Row 5: . . . | 2 . . | . . .
(4, 40, 2),
-- Row 6: 6 . . | . . . | . . .
(4, 46, 6),
-- Row 7: . . . | . 5 . | 7 . .
(4, 59, 5), (4, 61, 7),
-- Row 8: . 4 . | 9 . . | . . .
(4, 65, 4), (4, 67, 9),
-- Row 9: 1 . . | . . . | . . .
(4, 73, 1);

Creating The User Data Functions

With our database set up, we now need to create the UDFs that will act as the bridge between the Power BI report and our SQL stored procedures.

First, you’ll need to set up a connection to your Fabric SQL server within your UDF environment:

Next, we define our Python UDFs, each calling a specific stored procedure:

• initialize_game: Calls sp_InitializeGame
• set_cell_value: Calls sp_SetCellValue
• toggle_pencil_mark: Calls sp_TogglePencilMark
• clear_user_entries: Calls sp_ClearUserEntries

import fabric.functions as fn
import json
from datetime import datetime

udf = fn.UserDataFunctions()

@udf.connection(argName="sqlDB", alias="Game") 
@udf.function()
def initialize_game(sqlDB: fn.FabricSqlConnection, gameName: str, puzzleId: int) -> str:
    """
    Initialize a new Sudoku game from a puzzle template
    Prevents duplicate game names for the same puzzle
    
    Args:
        gameName: Name for the new game
        puzzleId: ID of the puzzle template to use
        
    Returns:
        Str with game initialization result
    """
    try:
        conn = sqlDB.connect()
        cursor = conn.cursor()
        
        # Check if game with same name and puzzle already exists
        cursor.execute("""
            SELECT COUNT(*) FROM Games 
            WHERE GameName = ? AND PuzzleId = ?
        """, (gameName, puzzleId))
        
        if cursor.fetchone()[0] > 0:
            return json.dumps(
                {
                    "status": "ERROR",
                    "message": "A game with this name already exists for this puzzle. Please choose a different name.",
                    "game_id": None
                }
            )
        
        # Execute the stored procedure
        cursor.execute("EXEC sp_InitializeGame ?, ?", (gameName, puzzleId))
        
        # Commit the transaction
        conn.commit()
        
        # Get the new game ID
        cursor.execute("""
            SELECT TOP 1 GameId, GameName, CreatedDate
            FROM Games 
            WHERE GameName = ? AND PuzzleId = ?
            ORDER BY CreatedDate DESC
        """, (gameName, puzzleId))
        
        game_details = cursor.fetchone()
        
        if game_details:
            return json.dumps(
                {
                    "status": "SUCCESS",
                    "message": "Game initialized successfully",
                    "game_id": game_details[0],
                    "gameName": game_details[1],
                    "puzzleId": puzzleId,
                    "created_date": game_details[2].isoformat(),
                }
            )
        else:
            return json.dumps(
                {
                    "status": "ERROR",
                    "message": "Failed to initialize game - game not found after creation",
                    "game_id": None
                }
            )
            
    except Exception as e:
        return json.dumps(
            {
                "status": "ERROR",
                "message": f"Database error: {str(e)}",
                "game_id": None
            }
        )
    finally:
        cursor.close() 
        conn.close()

@udf.connection(argName="sqlDB", alias="Game")
@udf.function()
def set_cell_value(sqlDB: fn.FabricSqlConnection, gameId: int, position: int, value: int) -> str:
    """
    Set a value in a Sudoku cell - now supports toggling off user-entered values
    
    Args:
        gameId: The game ID
        position: Cell position (1-81)
        value: Value to set (1-9)
        
    Returns:
        Str with operation result
    """
    try:
        # Validate inputs
        if not (1 <= position <= 81 and 1 <= value <= 9):
            return json.dumps(
                {
                    "status": "ERROR",
                    "message": "Invalid position or value. Position must be 1-81, value must be 1-9.",
                    "action_time": datetime.now().isoformat()
                }
            )
        
        conn = sqlDB.connect()
        cursor = conn.cursor()
        
        # Check current state of the cell
        cursor.execute("""
            SELECT CurrentValue, IsInitialValue FROM GameState 
            WHERE GameId = ? AND Position = ?
        """, (gameId, position))
        
        result = cursor.fetchone()
        
        # Determine the action that will be taken
        action_type = "set"
        if result:
            if result[1] == 1:  # IsInitialValue = 1
                return json.dumps(
                    {
                        "status": "ERROR",
                        "message": "Cannot modify initial puzzle values",
                        "action_time": datetime.now().isoformat()
                    }
                )
            elif result[0] == value:  # Same value already there
                action_type = "toggled_off"
        
        # Execute the stored procedure
        cursor.execute("EXEC sp_SetCellValue ?, ?, ?", 
                      (gameId, position, value))
        
        conn.commit()
        
        # Check final state to confirm action
        cursor.execute("""
            SELECT CurrentValue FROM GameState 
            WHERE GameId = ? AND Position = ?
        """, (gameId, position))
        
        final_result = cursor.fetchone()
        final_value = final_result[0] if final_result else None
        
        if action_type == "toggled_off":
            message = f"Value {value} toggled off at position {position}"
        else:
            message = f"Value {value} set at position {position}"
        
        return json.dumps(
            {
                "status": "SUCCESS",
                "message": message,
                "game_id": gameId,
                "position": position,
                "requested_value": value,
                "final_value": final_value,
                "action_type": action_type,
                "action_time": datetime.now().isoformat()
            }
        )
        
    except Exception as e:
        return json.dumps(
            {
                "status": "ERROR",
                "message": f"Database error: {str(e)}",
                "action_time": datetime.now().isoformat()
            }
        )
    finally:
        cursor.close() 
        conn.close()

@udf.connection(argName="sqlDB", alias="Game")
@udf.function()
def toggle_pencil_mark(sqlDB: fn.FabricSqlConnection, gameId: int, position: int, pencilValue: int) -> str:
    """
    Toggle a pencil mark in a Sudoku cell
    
    Args:
        gameId: The game ID
        position: Cell position (1-81)
        pencilValue: Pencil mark value (1-9)
        
    Returns:
        Str with operation result
    """
    try:
        # Validate inputs
        if not (1 <= position <= 81 and 1 <= pencilValue <= 9):
            return json.dumps(
                {
                    "status": "ERROR",
                    "message": "Invalid position or pencil value. Position must be 1-81, value must be 1-9.",
                    "action_time": datetime.now().isoformat()
                }
            )
        
        conn = sqlDB.connect()
        cursor = conn.cursor()
        
        # Check if cell has a value (cannot add pencil marks)
        cursor.execute("""
            SELECT CurrentValue FROM GameState 
            WHERE GameId = ? AND Position = ?
        """, (gameId, position))
        
        result = cursor.fetchone()
        if result and result[0] is not None:
            return json.dumps(
                {
                    "status": "ERROR",
                    "message": "Cannot add pencil marks to filled cells",
                    "action_time": datetime.now().isoformat()
                }
            )
        
        # Execute the stored procedure
        cursor.execute("EXEC sp_TogglePencilMark ?, ?, ?", 
                      (gameId, position, pencilValue))
        
        conn.commit()
        
        # Check if pencil mark was added or removed
        cursor.execute("""
            SELECT IsActive FROM PencilMarks 
            WHERE GameId = ? AND Position = ? AND PencilValue = ?
        """, (gameId, position, pencilValue))
        
        pencil_result = cursor.fetchone()
        is_active = pencil_result[0] if pencil_result else False
        action_type = "added" if is_active else "removed"
        
        return json.dumps(
            {
                "status": "SUCCESS",
                "message": f"Pencil mark {pencilValue} {action_type} at position {position}",
                "game_id": gameId,
                "position": position,
                "pencil_value": pencilValue,
                "is_active": is_active,
                "action_type": action_type,
                "action_time": datetime.now().isoformat()
            }
        )
        
    except Exception as e:
        return json.dumps(
            {
                "status": "ERROR",
                "message": f"Database error: {str(e)}",
                "action_time": datetime.now().isoformat()
            }
        )
    finally:
        cursor.close() 
        conn.close()

@udf.connection(argName="sqlDB", alias="Game")
@udf.function()
def clear_user_entries(sqlDB: fn.FabricSqlConnection, gameId: int) -> str:
    """
    Clear all user-entered values from a Sudoku game, keeping only initial puzzle values
    
    Args:
        gameId: The game ID
        
    Returns:
        Str with operation result
    """
    try:
        conn = sqlDB.connect()
        cursor = conn.cursor()
        
        # Verify game exists
        cursor.execute("SELECT GameName FROM Games WHERE GameId = ?", (gameId,))
        game_result = cursor.fetchone()
        
        if not game_result:
            return json.dumps(
                {
                    "status": "ERROR",
                    "message": "Game not found",
                    "action_time": datetime.now().isoformat()
                }
            )
        
        # Count user entries before clearing
        cursor.execute("""
            SELECT COUNT(*) FROM GameState 
            WHERE GameId = ? AND IsInitialValue = 0
        """, (gameId,))
        
        user_entries_count = cursor.fetchone()[0]
        
        # Count pencil marks before clearing
        cursor.execute("""
            SELECT COUNT(*) FROM PencilMarks 
            WHERE GameId = ? AND IsActive = 1
        """, (gameId,))
        
        pencil_marks_count = cursor.fetchone()[0]
        
        # Execute the stored procedure
        cursor.execute("EXEC sp_ClearUserEntries ?", (gameId,))
        
        conn.commit()
        
        return json.dumps(
            {
                "status": "SUCCESS",
                "message": f"Cleared {user_entries_count} user entries and {pencil_marks_count} pencil marks",
                "game_id": gameId,
                "game_name": game_result[0],
                "cleared_entries": user_entries_count,
                "cleared_pencil_marks": pencil_marks_count,
                "action_time": datetime.now().isoformat()
            }
        )
        
    except Exception as e:
        return json.dumps(
            {
                "status": "ERROR",
                "message": f"Database error: {str(e)}",
                "action_time": datetime.now().isoformat()
            }
        )
    finally:
        cursor.close() 
        conn.close()

Creating The Power BI Report

Now, let’s bring it all together in the Power BI report, following our architectural design.

Report Background

We’ll start by setting a background image that includes the Sudoku grid:

Puzzle Selector

To allow players to choose a puzzle, we’ll use the PuzzleTemplates table and add the PuzzleName field to a slicer so that the player can select a template that they want to play.

We’ll also need a DAX measure to pass the PuzzleId to the UDFs:

Selected PuzzleId = SELECTEDVALUE( PuzzleTemplates[PuzzleId] )

Number Selector

Players need to select a digit (1-9) to enter into the grid. A simple DAX table and a button slicer will do the trick:

SelectValue = GENERATESERIES( 1, 9, 1 )

Sudoku Grid

Instead of a standard Matrix visual, which has limitations in terms of visual customization, I opted for another button slicer, which has extensive formatting options and control. We’ll create a DAX table with 81 positions, one for each cell:

Grid Position = 
var tbl = GENERATESERIES( 1, 81, 1 )
return
SELECTCOLUMNS(
    tbl,
    "Position", [Value]
)

These button slicers will then be aligned with the Sudoku grid image in our background.

To display both the main cell values and the smaller pencil marks, we’ll use an SVG measure. This allows for dynamic and highly customizable cell content.

For SVG to render as a image instead of text the Data Category of the measure needs to be set to “Image URL”

Cell = 
var width = 40
var height = 40
var mainFontSize = 16
var pencilFontSize = 8
var isInitial = 
    IF(
        CALCULATE(
            MAXX( GameState, INT( GameState[IsInitialValue] ) )
            ,TREATAS( VALUES( 'Grid Position'[Position] ), GameState[Position] )
        ),
        " font-weight='bold'"
    )
var val = 
    CALCULATE(
        MAX( GameState[CurrentValue] )
        ,TREATAS( VALUES( 'Grid Position'[Position] ), GameState[Position] )
    )
var pencil = 
    CALCULATE(
        MAX( GameState[PencilMarks] )
        ,TREATAS( VALUES( 'Grid Position'[Position] ), GameState[Position] )
    )
return

"data:image/svg+xml;charset=utf8,
<svg xmlns='http://www.w3.org/2000/svg' width='"& width & "' height='"& height & "'>
    <text x='"& width / 2 & "' y='"& height /2 & "' font-size='" & mainFontSize & "' font-family='Segoe UI' dominant-baseline='middle' text-anchor='middle' fill='#333'" & isInitial & ">" & val & "</text>
    <text x='"& width / 2 & "' y='5' font-size='" & pencilFontSize & "' font-family='Segoe UI' dominant-baseline='middle' text-anchor='right' fill='#575555'>" & pencil & "</text>
</svg>"

Buttons

Finally, we’ll add four buttons, each linked to one of our UDFs. We’ll also need a couple of additional DAX measures to pass the current user’s name and selected game ID:

Selected GameName = USERPRINCIPALNAME()

Selected GameId = SELECTEDVALUE( GameState[GameId] )

The crucial step is to map the appropriate slicer fields and measures to the input parameters of each UDF:

Row Level Security

To ensure players only interact with their own games, we’ll apply RLS on the GameState table. This rule filters the data based on the logged-in user’s principal name:

vw_GameState[GameName] = USERPRINCIPALNAME()

For the role to apply to a player, after the report has been publish to service you need to go to the workspace and select … > security for the semantic model, and assign the player’s to the role.

Keep in mind that RLS doesn’t apply to workspace Admins. While I won’t be subject to it, other players will experience an isolated experience from other players.

Playing the Game!

With everything configured and published to the Power BI service we can play the game.

Conclusion

This project serves as a compelling proof-of-concept, demonstrating the power and flexibility of Translytical Task Flows in Power BI and Microsoft Fabric.Hopefully this lives up to being a spiritual successor to Phil’s initial work. While I’m pleased with the current state, there’s always room for improvement. Future enhancements could include:

• Highlighting cells with digits that match the currently selected cell
• Highlighting the row and column of the selected cell for better guidance
• Implementing a solver or hint feature
• Providing an indicator when a player enters an incorrect value
• A celebratory animation or message upon game completion
• Allowing players to enter pencil marks into multiple cells simultaneously