DAX question, filtering to specific set based on DAX epression

[BIanon]

Hello Community,

I am trying to replicate some logic we use for a certain way of identifying and grouping users.

Consider a virtual table with distinct userId, a Measure we will call "Value1".

UserId	Value1
1	6
2	9

I would like to then:
Order the table by [Value1] and add a rolling total of Value1, the rolling total will be used to group the users.

I figure out the split to use by creating variables like this:
VAR _HeavySplit = [Buyers] * 0.33
VAR _MediumSplit = [Buyers] * 0.66
VAR _LightSplit = [Buyers]


I then want to say: if RT < _HeavySplit then "Heavy" and so on.

I've figured out how to give the UserId's the needed tag in a not so pretty way but haven't yet been able to filter the table down to just holding the UserId's of a specifc group.

The end goal is a distinct list of userId's identified as eg. "Heavy" so I can apply it as a variable on other calculation like this:
CALCULATE( [Value2], _HeavyUsers )

Here is what I have working so far:

DEFINE

VAR _HeavySplit = [Value1] * 0.33
VAR _MediumSplit = [Value1] * 0.66
VAR _LightSplit = [Value1]

VAR _vtable = ADDCOLUMNS(
	VALUES('Purchase'[UserId] ),
	"Value1", [Value1],
	"RT", SWITCH( TRUE(), [Value1] + CALCULATE( [Value1], WINDOW(
            RANKX( ALLSELECTED( 'Purchase'[UserId] ), [Value1], , DESC ) * -1,
            REL,
            -1,
            REL,
            ALLSELECTED( 'Purchase'[UserId] ),
            ORDERBY( [Value1], DESC ) ) ) < _HeavySplit, "Heavy", [Value1] + CALCULATE( [Value1], WINDOW(
            RANKX( ALLSELECTED( 'Purchase'[UserId] ), [Value1], , DESC ) * -1,
            REL,
            -1,
            REL,
            ALLSELECTED( 'Purchase'[UserId] ),
            ORDERBY( [Value1], DESC ) ) ) < _MediumSplit, "Medium", "Light" )
	)
	

EVALUATE

_vtable
ORDER BY [Value1] DESC

This runs and correctly assigns a group to each ID but I am unsure how to only return users of a specific group

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result clearly.

[BIanon]

The end result is being able to calculate a given measure but only for the users identified as eg. "Heavy".

The segmentation needs to happen dynamically so my users can be explorative and try and different scenarios.

I am essentially trying to:
1. According to user input select all relevant users
2. Segment the users into Heavy, Medium and light (the segment is dynamic and changes if the user changes a slicer)
3. Calculate a measure for the context of just one of these segments
eg. CALCULATE( [Measure], _UsersInOneSegment )

I will get some mock data together and share it. Thank you.

I also want to note that I am in no way a DAX wizard so maybe my entire way of approaching the problem is bad.

[lbendlin]

The end goal is a distinct list of userId's identified as eg. "Heavy"

That cannot be your end goal.  Measures can use table variables during computation but they must eventually return a scalar value.

Change your goals.

[BIanon]

And it isn't - you cut out half the sentence? 

If I have a list of users an calculate a measure with those users as a filter, I'll get a scalar value computed only for those specific users eg.
CALCULATE( [Measure], _HeavyUsers )

would calculate a scalar for but with those users specifically as context

I do similar stuff all the time eg.
Identify two different sets of users based on user inputs, use INTERSECT(), NATURALINNERJOIN() etc to return a new set which is a join of the first two sets and then CALCULATE() a measure with that combined set as filter. This returns a scalar because it is still just one measure you are calculating, but you are just calculating it with a modified context as opposed to just calculating it as it was written