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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

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    | 75
b       | 1/2/21    | 75
c       | 1/1/21    | 30

c       | 1/2/21    | 20

c       | 1/3/21    | 100

The target sales for every month is 100. Now, we can see here for example user A failed on January, and B and C both failed on january and february. 

 

My goal here is to count how many and who failed repeatedly, in this case is b and c, in a measure.

 

The result if i put date and the measure together should look like this:

 

date          | repeater

1/2/2021  | 2

 

My current DAX looks like this:

 

failed count = COUNTX( FILTER( SUMMARIZE( Table, Table[user], CALCULATE( SUM(Table[sales])) < 100), Table[user])

 

previous failed count = COUNTX( FILTER( SUMMARIZE( Table, Table[user], CALCULATE( CALCULATE( SUM(Table[sales]), DATEADD(Calendar[Date], -1, MONTH)) < 100), Table[user])

 

repeater = IF(
NOT( ISBLANK( CALCULATE([failed count], DATESMTD(Table[date]))))
&&
NOT( ISBLANK( CALCULATE([failed count], DATESMTD( DATEADD( Table[date], -1, MONTH)))))
, 1, BLANK())

 

Tried this way but it returns blank.

 

Any help?

6 REPLIES 6
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
NaveenGandhi
Super User
Super User

Hello @aidelapplicate 

The below dax would provide the desired output you are looking for. Please add a index using PQ before implementing this.

 

Repeater =
 COUNTX(FILTER(SUMMARIZE('Table','Table'[Index],'Table'[date],'Table'[user],'Table'[sales],"Previous",CALCULATE(sum('Table'[sales]),ALL('Table'),'Table'[user]=EARLIER('Table'[user])&&('Table'[Index])=EARLIER('Table'[Index])-1),"Distinct_User_Month",CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table', 'Table'[User]),MONTH('Table'[Date]) = MONTH(EARLIER('Table'[Date])))),[Distinct_User_Month]=1&&'Table'[sales]<100&&[Previous]<100&&[Previous]<>BLANK()&&'Table'[sales]<>BLANK()),DISTINCTCOUNT('Table'[user]))
 
NaveenGandhi_1-1684491759642.png

 

Below is the dataset i used for this solution, I have taken care of same month failure as well.
NaveenGandhi_0-1684491743715.png

 

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

Thank you!

 

I see where your are going with this, and actually really thought it would work.

 

Unfortunately it returns blank on my side. For your info, sales column is actually a measure too. Derivated from some simple calculation. So i made few changes in your DAX a bit like use [sales] instead of Table[sales]. Do you think this affect the result?

o0llied
Frequent Visitor

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:

o0llied_0-1684486195067.png

 

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!

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.

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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