I created a Power BI learning game to demonstrate how Translytical and UDF capabilities can transform user journey and user experience in Power BI to boost adoption.
I focused on the following Translytical use cases:
1. Registration and State Management
The game begins with an engagement action : players choose to start and see their email recognized. This creates commitment to the learning experience. A UDF populates the SQL database table with active players, notifications, timestamps, avatars, progress, and points. Using DAX to control transparency based on written columns, the registration menu "disappears" for active players but reappears for new users opening the Power BI report.
2. Notification Management
I created personalized progress with guided steps to direct users through the prepared journey. Notifications attract player attention and explain game rules. This approach applies to any business report (eg: pushing notifications about report readiness, data accuracy, issues, guidance, etc. significantly boosts adoption). Users can dismiss notifications to avoid spam from already-consumed information.
3. Custom Time-Based Scoring
To demonstrate this functionality, users are guided to click a specific item and select an avatar. Avatar selection triggers a UDF that calculates progression speed and assigns scores based on time spent. This enables progress tracking and maintains learning engagement.
Result:
These three combined UDFs showcase new possibilities for user experience, storytelling and user journey design, accessibility and engagement levels that boost adoption. The report is fully personalized based on each player's progress, allowing players to start from where they left off. This showcases an example of personalized journey tracking.
Impact:
While gamification provides an excellent confidence boost and aids change management, these techniques extend far beyond gaming scenarios. By combining state management, notification handling, and adaptive scoring, organizations can create engaging user journeys that drive adoption and sustained usage. The techniques showcased here provide a foundation for any scenario requiring user engagement, progress tracking, and personalized experiences.
UDF used:
TRANSLYTICAL GAMING FRAMEWORK - UDF #1 of 3
Purpose: User registration and state management for personalized Power BI experiences
Business Value: Enables session persistence and user journey tracking
insert_gaming_user
import fabric.functions as fn
from datetime import datetime
# Create the UserDataFunctions instance
udf = fn.UserDataFunctions()
@udf.connection(argName="sqlDB", alias="AlexSQL")
@udf.function()
def insert_gaming_user(sqlDB: fn.FabricSqlConnection, userPrincipalName: str) -> str:
"""
Insert a new gaming user record with default values including timestamp
Args:
sqlDB: Connection to the Fabric SQL database
userPrincipalName: The user's principal name from DAX (e.g., from USERPRINCIPALNAME())
Returns:
str: Success message for Power BI
"""
try:
# Input validation
if not userPrincipalName or userPrincipalName.strip() == "":
raise fn.UserThrownError("User principal name cannot be empty.")
# Clean the input
userName = userPrincipalName.strip()
# Get current timestamp for when UDF is executed
current_timestamp = datetime.now()
# Establish connection to the SQL database
connection = sqlDB.connect()
cursor = connection.cursor()
# Check if user already exists (optional - remove if you want duplicates)
check_query = "SELECT COUNT(*) FROM [dbo].[Gaming] WHERE [UserName] = ?"
cursor.execute(check_query, userName)
user_count = cursor.fetchone()[0]
if user_count > 0:
cursor.close()
connection.close()
raise fn.UserThrownError(f"User '{userName}' already exists in the gaming table.")
# Generate next ID (since you're using INT NOT NULL PRIMARY KEY)
# Get the maximum existing ID and add 1
id_query = "SELECT ISNULL(MAX([Id]), 0) + 1 FROM [dbo].[Gaming]"
cursor.execute(id_query)
next_id = cursor.fetchone()[0]
# Insert the new gaming user record with extended columns including Points and Avatar ID
insert_query = """
INSERT INTO [dbo].[Gaming] (
[Id], [UserName], [BeginID], [NotificationStart], [TimeStamp],
[Level1], [L1_TimeStamp], [Level2], [L2_TimeStamp],
[Points], [ChallengePoints], [Avatar ID]
)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
"""
# Execute the insert with specified values
# TimeStamp = current datetime, Points and ChallengePoints = 0, Avatar ID = 2, other new columns = NULL
cursor.execute(insert_query, (
next_id, # Id
userName, # UserName
1, # BeginID
0, # NotificationStart
current_timestamp, # TimeStamp (current datetime)
None, # Level1 (NULL)
None, # L1_TimeStamp (NULL)
None, # Level2 (NULL)
None, # L2_TimeStamp (NULL)
0, # Points (set to 0)
0, # ChallengePoints (set to 0)
2 # Avatar ID (set to 2)
))
# Commit the transaction
connection.commit()
# Close connections
cursor.close()
connection.close()
return f"Successfully added user '{userName}' to gaming table with ID {next_id} and Avatar ID 2 at {current_timestamp.strftime('%Y-%m-%d %H:%M:%S')}"
except fn.UserThrownError:
# Re-raise user errors as-is
raise
except Exception as e:
# Handle any other database errors
raise fn.UserThrownError(f"Database error occurred: {str(e)}")
TRANSLYTICAL GAMING FRAMEWORK - UDF #2 of 3
Purpose: Notification management and user engagement flow control
Business Value: Enables dismissible notifications and prevents information spam, applicable to any business report requiring guided user experiences
update_notification_status
import fabric.functions as fn
# Create the UserDataFunctions instance
udf = fn.UserDataFunctions()
@udf.connection(argName="sqlDB", alias="AlexSQL")
@udf.function()
def update_notification_status(sqlDB: fn.FabricSqlConnection, userPrincipalName: str) -> str:
"""
Update the NotificationStart column to 1 for an existing user
Args:
sqlDB: Connection to the Fabric SQL database
userPrincipalName: The user's principal name from DAX (e.g., from USERPRINCIPALNAME())
Returns:
str: Success message for Power BI
"""
try:
# Input validation
if not userPrincipalName or userPrincipalName.strip() == "":
raise fn.UserThrownError("User principal name cannot be empty.")
# Clean the input
userName = userPrincipalName.strip()
# Establish connection to the SQL database
connection = sqlDB.connect()
cursor = connection.cursor()
# Check if user exists
check_query = "SELECT COUNT(*) FROM [dbo].[Gaming] WHERE [UserName] = ?"
cursor.execute(check_query, userName)
user_count = cursor.fetchone()[0]
if user_count == 0:
cursor.close()
connection.close()
raise fn.UserThrownError(f"User '{userName}' does not exist in the gaming table.")
# Update the NotificationStart column to 1
update_query = """
UPDATE [dbo].[Gaming]
SET [NotificationStart] = 1
WHERE [UserName] = ?
"""
# Execute the update
cursor.execute(update_query, userName)
# Commit the transaction
connection.commit()
# Close connections
cursor.close()
connection.close()
return f"Successfully updated NotificationStart to 1 for user '{userName}'"
except fn.UserThrownError:
# Re-raise user errors as-is
raise
except Exception as e:
# Handle any other database errors
raise fn.UserThrownError(f"Database error occurred: {str(e)}")
TRANSLYTICAL GAMING FRAMEWORK - UDF #3 of 3
Purpose: Custom time-based scoring and progress tracking with avatar personalization
Business Value: Measures user engagement speed and rewards timely interactions, adaptable for training completion, task performance, and user adoption metrics
complete_level1_challenge
import fabric.functions as fn
from datetime import datetime
# Create the UserDataFunctions instance
udf = fn.UserDataFunctions()
@udf.connection(argName="sqlDB", alias="AlexSQL")
@udf.function()
def complete_level1_challenge(sqlDB: fn.FabricSqlConnection, userPrincipalName: str, selectedAvatar: int) -> str:
"""
Complete Level 1 challenge, assign points based on time difference, and update Avatar ID
Args:
sqlDB: Connection to the Fabric SQL database
userPrincipalName: The user's principal name from DAX (e.g., from USERPRINCIPALNAME())
selectedAvatar: The selected avatar ID from DAX measure
Returns:
str: Success message with points awarded for Power BI
"""
try:
# Input validation
if not userPrincipalName or userPrincipalName.strip() == "":
raise fn.UserThrownError("User principal name cannot be empty.")
if selectedAvatar is None:
raise fn.UserThrownError("Selected Avatar cannot be empty.")
# Clean the input
userName = userPrincipalName.strip()
# Get current timestamp for L1_TimeStamp
current_timestamp = datetime.now()
# Establish connection to the SQL database
connection = sqlDB.connect()
cursor = connection.cursor()
# Check if user exists and get their initial TimeStamp
check_query = """
SELECT [TimeStamp], [L1_TimeStamp]
FROM [dbo].[Gaming]
WHERE [UserName] = ?
"""
cursor.execute(check_query, userName)
result = cursor.fetchone()
if not result:
cursor.close()
connection.close()
raise fn.UserThrownError(f"User '{userName}' does not exist in the gaming table.")
initial_timestamp = result[0]
existing_l1_timestamp = result[1]
# Check if Level 1 challenge already completed
if existing_l1_timestamp is not None:
cursor.close()
connection.close()
raise fn.UserThrownError(f"User '{userName}' has already completed Level 1 challenge.")
# Check if initial TimeStamp exists
if initial_timestamp is None:
cursor.close()
connection.close()
raise fn.UserThrownError(f"User '{userName}' does not have an initial TimeStamp.")
# Calculate time difference
time_diff = current_timestamp - initial_timestamp
total_minutes = time_diff.total_seconds() / 60
# Determine points based on time difference
# GAMING MECHANICS: Time-based scoring rewards faster completion
# < 10 min = 20 pts (expert), < 1 hour = 10 pts (good), etc.
if total_minutes < 10:
points = 20
time_category = "< 10 minutes"
elif total_minutes < 60: # < 1 hour
points = 10
time_category = "< 1 hour"
elif total_minutes < 1440: # < 1 day (24 hours * 60 minutes)
points = 5
time_category = "< 1 day"
else: # more than 1 day
points = 3
time_category = "> 1 day"
# Update L1_TimeStamp, Level1 points, and Avatar ID
update_query = """
UPDATE [dbo].[Gaming]
SET [L1_TimeStamp] = ?, [Level1] = ?, [Avatar ID] = ?
WHERE [UserName] = ?
"""
# Execute the update
cursor.execute(update_query, (current_timestamp, points, selectedAvatar, userName))
# Commit the transaction
connection.commit()
# Close connections
cursor.close()
connection.close()
return f"Level 1 completed for '{userName}': {points} points awarded ({time_category}), Avatar ID updated to {selectedAvatar} at {current_timestamp.strftime('%Y-%m-%d %H:%M:%S')}"
except fn.UserThrownError:
# Re-raise user errors as-is
raise
except Exception as e:
# Handle any other database errors
raise fn.UserThrownError(f"Database error occurred: {str(e)}")
If this Translytical entry resonates with you, I'd love to connect!
Please give it a ❤️ if you found it valuable, and feel free to reach out on LinkedIn
Best regards,
Alexandru Badiu
