Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Distinct count how many users failed repeatedly

Hi there,   Suppose i have this table (date is in dd/mm/yyyy):   user  | date       | sales a       | 1/1/21    | 80 a       | 1/2/21    | 110 a       | 1/3/21    | 150 b       | 1/1/21    | ...
Need Help
Show and Tell
Tips and Tricks
o0llied's avatar
o0llied
Frequent Visitor
3 years ago

Hello aidelapplicate,

 

I think i understand your issue and have created a powerbi report based on your example data set.

I got the following results while trying to create what you described:

 

I hope this is what you are looking to do.

I have created this using the following two measures:

failed count = 
CALCULATE(
    COUNT(Data[User]),
    Data[sales] < 100
)
repeater = 
IF( 
    [failed count] > 1,
    CALCULATE(
        COUNT(Data[User]),
        Data[sales] < 100
    ),
    BLANK()
)

 

You can download my pbix file here: download pbix file 

 

Hope this helps.

 

If this solved your problem, please give a thumbs up and mark this answer as your solution 🙂

 

Have a nice day!

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Thank you so much for your replies.

     

    Forgot to mention, i actually wants to find the repeater in the following month. if the user failed more than once in the same month, that doesnt count as repeater. if the user failed in month 1 and month 2, means the user is a repeater in the month 2. hope this clarifies.

    • o0llied's avatar
      o0llied
      Frequent Visitor
      3 years ago

      I see, you can add a check to the repeater to look at the previous month like this:

      repeater = 
IF( 
    [failed count] > 1,
    CALCULATE(
        COUNT(Data[User]),
        DATEADD(Data[Date], -1, MONTH),
        Data[sales] < 100
    ),
    BLANK()
)

       

      Hope this helps!