Forum Discussion

amigi's avatar
amigi
New Member
9 years ago

Conditioned count per user

Hi all, I have a table similar to the example below. I would like to claculate the number of users that had at least 2 days of transactions in the last 3 days. in the example below the value for m...
Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi amigi，

Firstly, create a calculated column using formula below.

checktrans = IF(Table[number of transactions ]=0, 0,1)

Secondly, create the following measures and create a table visual as shown in the screenshot below, then check if it returns your expected result.

rolling trans per user = CALCULATE(SUM(Table[checktrans]),FILTER(ALL(Table),Table[Date]<=max(Table[Date])),VALUES('Table'[UserID]))

Measure = CALCULATE(COUNTA(Table[UserID]),FILTER(Table,Table[rolling trans per user]>=2))



 

Thanks,
Lydia Zhang

amigi's avatar
amigi
New Member
9 years ago

Thansk Lydia (Anonymous)!

 

I think that it indeed helps with the "groupby" functianlity, however it seems like it misses the "last 3 days" condition. (claculate the number of users that had at least 2 days of transactions in the last 3 days).    right?

 

I assume that this condition should be expressed here?

rolling trans per user = CALCULATE(SUM(Table[checktrans]),FILTER(ALL(Table),Table[Date]<=max(Table[Date])),VALUES('Table'[UserID]))

 

but I can't understand this filter expression. 

could you please help me in understanding it?  and how to express the "in the last X days" condition?

 

Thanks!

Igi

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi amigi,

    You only want to get data of March 17, right? If so, create a column using  the DAX below and use it to filter your visual.

    Column = IF(Table[Date ]=TODAY()-5,1,0)



    Thanks,
    Lydia Zhang