Conditional Dynamic Dax Segregation on customer

[AlanP514]

Hi All ,
I am expecting Help on this to achive this logic
I need to segregate user into three categories
Active Customer, Super Active Customer,Hyper Active Customer

Based on the year and month selection applying  in the power bi report 
customer segregation should happen

I have two slicers one is Year, and Month  Based on the these selection my power bi pivot table will work dynamically from selected month to last 6 months for eg (I am selecting September 2023 from slicer my power bi pivot table will work from septmber to April)
Diagram :1 

I need to segregate customer based on the above pivot table into Active Customer, Super Active Customer,Hyper Active Customer
For these need consider these conditions

1 . Active Customer =  if the Count of Login time is greater than equal to 1 for the entire selected 6 months and if minimum value is (1 ,2)then that user is active customer

2. Super Active Customer =  if the Count of Login time is greater than equal to 3 for the entire selected 6 months and if minimum value is 3 or 4 then that user is Super active Customer

3.Hyper Active Customer =  if the Count of Login time is greater than equal to 5 for the entire selected 6 months and if minimum value is 5 then that user is Hyper active Customer

I am attaching the data model also here

This is the data model dynamic month name is coming from Previous date calendar table and month slicer is coming from Calendar table and user id and login is coming from fact tracking table
Appreciate Help on this to achive the logic 

[123abc]

To achieve customer segregation in Power BI based on your specified conditions, you can use DAX measures and calculated columns. Here's a step-by-step guide to implement this logic:

1. Create a Date Table: Ensure you have a date table in your Power BI model that includes columns like Year, Month, and Date.

2. Create a Fact Table: Make sure you have a fact table that contains user IDs, login times, and a relationship to your date table based on the date of login.

3. Create a DAX Measure for Count of Logins: Create a measure that calculates the count of logins for the selected 6 months. You can use the FILTER function to filter the data based on the selected Year and Month slicers. The DAX formula might look like this:

Login Count =
CALCULATE(
COUNT('FactTrackingTable'[Login]),
FILTER(
'FactTrackingTable',
'FactTrackingTable'[Date] >= MIN('DateTable'[Date]) &&
'FactTrackingTable'[Date] <= MAX('DateTable'[Date])
)
)

Create a DAX Measure for Minimum Value of Logins: Create a measure that calculates the minimum login value for the selected 6 months. The DAX formula might look like this:

Min Login Value =
CALCULATE(
MIN('FactTrackingTable'[Login]),
FILTER(
'FactTrackingTable',
'FactTrackingTable'[Date] >= MIN('DateTable'[Date]) &&
'FactTrackingTable'[Date] <= MAX('DateTable'[Date])
)
)

Create DAX Measures for Customer Segmentation: Now, you can create three DAX measures to categorize customers based on your conditions:

Active Customer:

Active Customer =
IF(
[Login Count] >= 1 && [Min Login Value] >= 1 && [Min Login Value] <= 2,
"Active Customer",
BLANK()
)

Super Active Customer:

Super Active Customer =
IF(
[Login Count] >= 3 && [Min Login Value] >= 3 && [Min Login Value] <= 4,
"Super Active Customer",
BLANK()
)

Hyper Active Customer:

Hyper Active Customer =
IF(
[Login Count] >= 5 && [Min Login Value] >= 5,
"Hyper Active Customer",
BLANK()
)

1. Display the Results: Add these customer segmentation measures to your Pivot Table in Power BI, and they will dynamically categorize customers based on the selected Year and Month slicers.

Now, when you select a Year and Month in your slicers, your Pivot Table will display the customers categorized as Active, Super Active, or Hyper Active based on the conditions you specified.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[AlanP514]

Hi @123abc 
thanks for the reply ,
i am attachign the mock data, min login value which u created i cant create becasue i am counting total logins sum of mobile count and desktop count on  data, i will attach you the mock data and thing swhich i did in pbi, kindly have a look

for min expression we cant call a measure , we can call only direct column