Hi All,
I have tried to find the right DAX calculation to solve this problem without success, so I need your expertise to find the right solution. The question I want to solve is "How many unique UIDs that are repeat offenders (Column ‘Offender’ > 0) and a count of reps appearing in appearing in more than 1 (of the selected) months?
Hello @mmurillo7gt,
Thank You for posting your query onto the Forum.
Can you please upload the working of the file for the reference? So that members of our forum can assist you better and provide you results efficiently.
Thanks and Warm Regards,
Harsh
Example.xlsx (26.6 KB)
@mmurillo7gt Why is 13 included? He is an offender ( >0 ) once in only 1 month.
Yes it should be not included. because it is not a repeat offender Number 13
Yes, I have updated the example and the picture to avoid confusion. Thank you for pointing out!
@mmurillo7gt Looks like it didn’t update, can you check again.
@AntrikshSharma I uploaded the example xls and picture. Could you check if it is uploaded? Thank you
This was a bit trickier than it looked at first glance. See if this works for you:
MonthCount =
CALCULATE(
DISTINCTCOUNT( Data[YYYYMM] ),
ALLEXCEPT( Data, Data[UID] )
)
Summary Offenders =
VAR vTable0 = CALCULATETABLE(
Data,
Data[Offender] > 0
)
VAR Vtable1 = ADDCOLUMNS(
SUMMARIZE( vTable0, Data[Channel], Data[UID] ),
"@monthcount", [MonthCount]
)
VAR vTable2 = FILTER( Vtable1, [@monthcount] > 1 )
VAR Result = COUNTROWS( vTable2 )
RETURN
Result
The one question I had is that UID 20 seems like it should be included based on your criteria. Please let me know if I’ve interpreted that incorrectly.
I hope this is helpful. Full solution file attached below.
Okay, just saw your additional posts above – will need to revise my measure based on the new understanding of repeat offender.
Thank you Brian! yes the 20 was not included because the offender column is 0 in 202006 and in 202007 the offender column is 5. It needs to be repeat offenders appearing in more than 1 month.
Okay, simple change to the first measure, just adding an additional filter condition.
See if this does the trick:
MonthCount =
CALCULATE(
DISTINCTCOUNT( Data[YYYYMM] ),
ALLEXCEPT( Data, Data[UID] ),
Data[Offender] > 0
)
Summary Offenders =
VAR vTable0 = CALCULATETABLE(
Data,
Data[Offender] > 0
)
VAR Vtable1 = ADDCOLUMNS(
SUMMARIZE( vTable0, Data[Channel], Data[UID] ),
"@monthcount", [MonthCount]
)
VAR vTable2 = FILTER( Vtable1, [@monthcount] > 1 )
VAR Result = COUNTROWS( vTable2 )
RETURN
Result
@mmurillo7gt Similarly 15 shouldn’t be included? Can you spend some time in checking what exactly should be included. It will help us in providing a solution quickly.
My understanding is no, because offender status in 202006 = 0 . This was what I changed in my second version.
Thank you Brian! Wow you did it so fast! I am checking the code on my dashboard.
It’s great to know that you are making progress with your query @mmurillo7gt. Please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!
Thank you @AntrikshSharma! It works perfectly. I really appreciate your help. Have a fantastic day
@EnterpriseDNA How do I change it to Solve it?
I got you covered. I’ve already marked it, but for future reference just go to the post that solved the issue, click on the three dots and then click on the solved box per the screenshot below.
Glad the solution worked well for you.
