How to calculate number of users performing n number of users with criteria applied

[AdaL02]

HI all, I'm new to PowerBi. I had a similar post couple of days ago but I need new tweak.

I want to calculate number of users who performed 1 action, 2 actions, 3 etc.. but now, I only want to count number of actions after the user performed "open" action.

for example, in the dataset below, Adrian theoratically did 4 actions. However, to my analysis, he did only 2 actions. Message sent is going to apply to everyone, and I only want to count actions after there is open.

user name     actions     action value

Adrian            call             30%

Adrian           video          click

Adrian           open          open

Adrian          Message        sent

Bob              open           open

Bob           Message         sent

Charlie       Message        sent

Charlie        open            open

Charlie       video             click

Daniel      message          sent

and therefore, based on new context, my final results should be like this:

#actions          #users

0                          1 (Bob, didn't do anything after open)

1                          1 (Charlie, clicked video after open)

2                          1 (Adrian, called and clicked video)

Daniel will not be counted because he never opened the message.

Thanks to share your guidance!

cheers,

Ada

my earlier question on the same topic, though without this tweak required is here 

[v-deddai1-msft · Accepted solution]

Hi @AdaL02 ,

As you description, the output based on your sample data should be :

#actions          #users

0                          1

1                          0

2                          3

And you can try the following method. Create an index column in your sample data:

Then you can create a new calculated table:

Table2 =
ADDCOLUMNS (
    SELECTCOLUMNS ( { 0, 1, 2 }, "#actions", [Value] ),
    "#users",
        CALCULATE (
            COUNT ( 'Table'[user name] ),
            FILTER (
                VALUES ( 'Table'[user name] ),
                VAR a =
                    CALCULATE ( MAX ( 'Table'[Index] ), 'Table'[actions] = "open" )
                VAR b =
                    MAX ( 'Table'[Index] )
                RETURN
                    IF ( ISBLANK ( a ), 0, b - a ) = [#actions]
            )
        ) + 0
)

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

Best Regards,

Dedmon Dai

[Tahreem24]

@AdaL02  try this measure:

Measure = CALCULATE(COUNT('TableName'[UserName]),'TableName'[Action] in {"Video","Call"})

[amitchandak]

@AdaL02 , Add an index column and try a measure like

https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi

Measure =
VAR __id = MAX ('Table'[user name] )
VAR __date = CALCULATE ( MAX('Table'[Index] ), ALLSELECTED ('Table' ), 'Table'[user name] = __id && 'Table'[action value] = "Open")
CALCULATE ( Count ('Table'[user name] ), VALUES ('Table'[user name] ),'Table'[user name] = __id,'Table'[Index] >= __date )