Dynamic segmentation of the Customer's into 3 Category's Based on Date Slicer value

DAX DAX Calculations
1

Dear Dax Experts,

I had a requirement to segment the customer into three categories like (Most Engaged, Moderate Engage, Least Engage) based on “Duration” In Power BI
and Segmentation needs to be dynamically calculated based on date slicer filter.

Note : Looking for Equivalent in DAX for NTile in SQL and that needs to be dynamically calculate based on date range in slicer.

– Sample Data
Create Table table_A
(
UserId varchar(15), Duration int , date date
)

INSERT INTO table_A
SELECT ‘User 1’, 10,Cast(Getdate()-25 as date)
union SELECT ‘User 1’, 13,Cast(Getdate()-24 as date)
union SELECT ‘User 2’, 13,Cast(Getdate()-24 as date)
union SELECT ‘User 2’, 13,Cast(Getdate()-25 as date)
union SELECT ‘User 3’, 20,Cast(Getdate()-25 as date)
union SELECT ‘User 9’, 2,Cast(Getdate()-25 as date)
union SELECT ‘User 5’, 7,Cast(Getdate()-25 as date)
union SELECT ‘User 7’, 8,Cast(Getdate()-25 as date)
union SELECT ‘User 6’, 10,Cast(Getdate()-25 as date)
union SELECT ‘User 1’, 12,Cast(Getdate()-6 as date)
union SELECT ‘User 9’, 20,Cast(Getdate()-6 as date)
union SELECT ‘User 4’, 1,Cast(Getdate()-25 as date)
union SELECT ‘User 7’, 13,Cast(Getdate()-25 as date)

SELECT * FROM table_A
image

– Expected Output
SELECT UserId,Duration, **NTILE(3) OVER (ORDER BY Duration ) AS Bucket
FROM
(SELECT UserId,Sum(Duration) AS Duration
FROM table_A
WHERE Date>=‘2022-10-18’ AND Date<=‘2022-10-19’
GROUP BY UserId) AS x
image

Attached Working PBI file for reference.

Ntile Customer Segment in PBI.pbix (62.2 KB)

2

Hi @mrommala,

Unfortunately I didn’t manage to download new version of PBI so currently I could not open your pbix file
so maybe someone will provide much better answer.

But in meantime - maybe you can check:

Best regards,
Maja