Hi @binayjethwa ,
To achieve this, you'll first create a calculated table to group the completion times into buckets like "0-30 days". Then, you will write a series of DAX measures—one for each column in your desired table—that count the users who fall into these buckets. Before you begin, it's essential that your data model has an active relationship from your date table to Fact Table[Access Date] and an inactive relationship from your date table to Fact Table[Completed Date]. This setup is key to dynamically filtering by completion dates.
First, create a calculated table that defines the time buckets. In Power BI, select New table and enter the following DAX expression. After creating it, remember to select the Category column and use the "Sort by column" feature to sort it by the SortOrder column. This will ensure your table visual's rows are in the correct order.
DaysBucket =
DATATABLE (
"Category", STRING,
"MinDays", INTEGER,
"MaxDays", INTEGER,
"SortOrder", INTEGER,
{
{ "0-30 days", 0, 30, 1 },
{ "31-60 days", 31, 60, 2 },
{ "61-90 days", 61, 90, 3 },
{ "91-120 days", 91, 120, 4 },
{ "121-180 days", 121, 180, 5 },
{ "180+ days", 181, 10000, 6 }
}
)
Next, you will create the DAX measures. Each measure will be built on a reusable logic that generates a temporary summary table, UserStats. This virtual table is calculated on the fly and contains the key metrics for each user based on any active slicers: their total number of completed certifications and the number of days it took from their first access to their final completion. This is where the inactive relationship is activated using USERELATIONSHIP to filter completions by the selected date range.
The first measure calculates the total number of users for each time bucket, which will become the first column in your table.
Users (Total) =
VAR UserStats =
ADDCOLUMNS (
SUMMARIZE (
CALCULATETABLE(
'Fact Table',
USERELATIONSHIP('Fact Table'[Completed Date], 'Dim: Role Playing Date'[Dim Date]),
NOT ISBLANK('Fact Table'[Completed Date])
),
'Fact Table'[User ID]
),
"TotalCompletions", CALCULATE ( DISTINCTCOUNT ( 'Fact Table'[Asset ID] ) ),
"DaysToComplete",
CALCULATE (
DATEDIFF ( MIN ( 'Fact Table'[Access Date] ), MAX ( 'Fact Table'[Completed Date] ), DAY )
)
)
VAR MinDays = SELECTEDVALUE ( 'DaysBucket'[MinDays] )
VAR MaxDays = SELECTEDVALUE ( 'DaysBucket'[MaxDays] )
VAR Result =
COUNTROWS (
FILTER (
UserStats,
[DaysToComplete] >= MinDays && [DaysToComplete] <= MaxDays
)
)
RETURN Result
Now, create the measure for your next column, which filters for users who completed a specific number of certifications. This follows the exact same pattern as the Users (Total) measure but adds a condition to filter the TotalCompletions for each user.
Users 1-30 Certs =
VAR UserStats =
ADDCOLUMNS (
SUMMARIZE (
CALCULATETABLE(
'Fact Table',
USERELATIONSHIP('Fact Table'[Completed Date], 'Dim: Role Playing Date'[Dim Date]),
NOT ISBLANK('Fact Table'[Completed Date])
),
'Fact Table'[User ID]
),
"TotalCompletions", CALCULATE ( DISTINCTCOUNT ( 'Fact Table'[Asset ID] ) ),
"DaysToComplete",
CALCULATE (
DATEDIFF ( MIN ( 'Fact Table'[Access Date] ), MAX ( 'Fact Table'[Completed Date] ), DAY )
)
)
VAR MinDays = SELECTEDVALUE ( 'DaysBucket'[MinDays] )
VAR MaxDays = SELECTEDVALUE ( 'DaysBucket'[MaxDays] )
VAR Result =
COUNTROWS (
FILTER (
UserStats,
[DaysToComplete] >= MinDays && [DaysToComplete] <= MaxDays &&
[TotalCompletions] >= 1 && [TotalCompletions] <= 30
)
)
RETURN Result
You can create the rest of your required measures by simply copying the Users 1-30 Certs measure and adjusting the filter condition for the completion count. For example, the filter for your "31-80" bucket would be [TotalCompletions] >= 31 && [TotalCompletions] <= 80, and the filter for "100+" would be [TotalCompletions] >= 100.
Finally, build your visual. Drag a Table visual onto the Power BI canvas. Add the DaysBucket[Category] field, followed by each of the measures you created (Users (Total), Users 1-30 Certs, etc.) into the Values field well. When you add slicers for your date dimension or certification names, the entire table will dynamically update.
Best regards,
