Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
bigfun
Helper I
Helper I

Measure to Segment User Activity

I created a measure that totals up 'Logins' per clientID a month.

 

Monthly Logins = calculate(DISTINCTCOUNT(ui_PageActionView[UnqSession]), DATESINPERIOD('Calendar'[Date],max('Calendar'[Date]),-1,MONTH))
 
bigfun_1-1663261039778.png

 

But now I want to create another measure that assigns a 'level of activity' based on the # of monthly logins

Low = less than 5
Medium = between 5 - 10

HIgh = 10+

 

I believe I need to create it using a switch but I'm not sure how to set it up.

 

 

 

1 ACCEPTED SOLUTION
rsbin
Super User
Super User

@bigfun ,

Yes, SWITCH Statement is how I would approach this as well:

LevelofActivity = SWITCH(
                      TRUE(),
                      [Monthly Logins] > 10, "High",
                      [Monthly Logins] >=5, "Medium",
                      "Low" )

The last line, is your "else" condition.  You can specify your third condition <5 if you want to make it clearer to yourself or others.

Hope this helps.

Regards,

View solution in original post

7 REPLIES 7
bigfun
Helper I
Helper I

So i am not sure why... But all the 'Activity Status' are showing low... I'm not sure why it isn't evaluating the number and assigning the status correctly.

 

Sample File

@bigfun ,

The LevelofActivity is written as a Measure not a Calculated Column.

If you drag this Measure into your Visual in your Original Post, it should work as intended.

 

As a Calculated Column, it is evaluating each Row to 1, hence "low".  I believe this is what's happening.

rsbin
Super User
Super User

@bigfun ,

Yes, SWITCH Statement is how I would approach this as well:

LevelofActivity = SWITCH(
                      TRUE(),
                      [Monthly Logins] > 10, "High",
                      [Monthly Logins] >=5, "Medium",
                      "Low" )

The last line, is your "else" condition.  You can specify your third condition <5 if you want to make it clearer to yourself or others.

Hope this helps.

Regards,

This is perfect... now I just need to figure out how to free up or not use as much memory processing it.. haha

@bigfun ,

Glad I could help.  I use SWITCH throughout all of my numerous reports.  Have never had an issue with memory or resources.  Perhaps something else in your model is causing this.  Unfortunately, this is out of my scope.

Curious if you have even put a condition on a switch statement.. For example:

I have a column that has a version# 1.0, 2.0, etc, and I only want to run the switch statement on the rows that are version 2.0.

@bigfun ,

You can try this a couple of different ways:

LevelofActivity = SWITCH(
                      TRUE(),
                      [Version#] <> 2.0, Blank(),  // or "Not Applicable" or whatever....
                      [Monthly Logins] > 10, "High",
                      [Monthly Logins] >=5, "Medium",
                      "Low" )

If that doesn't work, then apply the condition to each line of conditions:

LevelofActivity = SWITCH(
                      TRUE(),
                      [Version#] = 2.0 && [Monthly Logins] > 10, "High",
                      [Version#] = 2.0 && [Monthly Logins] >=5, "Medium",
                      [Version#] = 2.0 && [Monthly Logins] < 5  "Low" )

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.