The SELECTEDVALUE function in DAX is a powerful tool designed to return a single value from a column when one, and only one, value is selected. If no value or multiple values are selected, it returns either a default value (if provided) or BLANK(). Syntax: SELECTEDVALUE(<ColumnName>[, <DefaultValue>]) How SELECTEDVALUE Works When exactly one value is selected from…
Author: businessintelligence_6wn2oz
Understanding the Power of Common Table Expressions (CTEs) and RANK() in SQL
When working with relational databases, leveraging Common Table Expressions (CTEs) and ranking functions like RANK() can unlock significant potential for analyzing and organizing your data. This blog post will break down the concept of CTEs, the RANK() function, and how they can be combined effectively. What is a Common Table Expression (CTE)? A Common Table Expression (CTE) is a temporary result…
Analyzing Median, Average, and Range of Sale Prices Across Four Ontario Cities Using SQL
In this blog post, we’ll break down the SQL query provided and explain how it calculates essential statistics—namely the median, average, and range of sale prices—across four cities in Ontario: London, Waterloo, St. Thomas, and St. Catharines. The query uses window functions and aggregation to provide insightful data about the real estate market trends in…
“Finding Above-Average Earners by Department with Nested Subqueries in SQL”
A nested subquery in SQL is a query within another query. The inner query (subquery) is executed first, and its result is used by the outer query. Here’s a classic example using a nested subquery to find employees earning more than the average salary of their department. Example Schema Consider the following two tables: 2….
Using the RankX Function in Power BI for Dynamic Rankings
Power BI is a powerful tool that offers multiple ways to transform and visualize your data. One of its most helpful features is the RankX function, which allows you to rank items dynamically based on a measure, such as total sales or revenue. In this blog post, we’ll walk through a simple example of how…
Managing Dynamic Arrays in Crystal Reports: Step-by-Step Guide
Dataset of the example used Crystal Reports is a powerful tool for generating dynamic reports, and managing arrays can significantly enhance its functionality. However, Crystal Reports doesn’t directly support returning arrays from formulas, so we must work within its constraints using global variables and carefully crafted formulas. This guide walks you through how to create,…
“Building Conditional Running Totals in Crystal Reports: Incorporating Next and Previous Record Logic”
In Crystal Reports, creating a manual running total based on specific conditions (e.g., next or previous records) involves using variables and formulas. Here’s an example: Scenario: You want to calculate a manual running total for sales, but only for records where the sale amount is greater than a certain threshold, say $1000. Steps: 1. Create…
Case Study: Automating Funds Disbursement Reporting for a Casino Client
Case Study: Automating Funds Disbursement Reporting for a Casino Client Client Challenge Our client, a prominent casino operator, faced a complex reporting requirement for their funds disbursement transactions. They were required to submit detailed reports to FINTRAC and other regulatory bodies, documenting each financial transaction made by customers. This reporting structure was particularly intricate because…
Case Study: Automated Reporting System for Healthcare Pharmacy Client
Background Our team was approached by a healthcare client in the pharmacy sector who faced challenges with their existing reporting system. The client’s management relied heavily on reports developed in Excel to track pharmacy performance metrics, such as year-over-year and month-over-month pharmacy revenues, as well as a matrix comparing performance by physician. Additionally, they wanted…
Case Study: Leveraging Business Intelligence to Streamline Disability Case Costing in Healthcare
Case Study: Leveraging Business Intelligence to Streamline Disability Case Costing in Healthcare Client Challenge A healthcare company specializing in disability case management approached us with a significant problem: despite careful case costing efforts, the actual costs were consistently exceeding estimates. This company had a contract with a major client, managing disability cases at a fixed…