Count the repetition of values in last 5 days in DAX

Question

I have a Anti Virus scan dataset which gets generated on daily basis and goes many days back in the past, in this i have to calculate number of times a machine is appearing a Non-Compliant in last 5 days, Currently I am using below DAX formula to create calculated column

Repetition = CALCULATE(COUNT('SCCM Antivirus'[MachineName]),ALLEXCEPT('SCCM Antivirus','SCCM
 Antivirus'[MachineName]),'SCCM Antivirus'[ComplianceStatus]= "Non-
Compliant",'SCCM Antivirus'[HRs]<= 120)

But, The problem is that every machine has multiple appids hence instead of calculating 1 count per day per machine, which should be maximum 5 for 5 last days, i get count(appid),

e.g. in the attached table, if i want to see how many times Machine 'A' has repeated in last two days 11/03 and 10/03 then it should give me a count of 2, and that is what my requirement is also, but instead i get count of 5, as Machine A has 2 Appid on 11/03 and 3 Appid on 10/03, and this is my problem, i Want distinct count of machine group by date for last 5 days only. Could some please help me.

enter image description here

Try DISTINCTCOUNT('SCCM Antivirus_RBF'[Date]) in stead of COUNT('SCCM Antivirus_RBF'[MachineName]) — Marco Vos
Did not work, I am getting 25 repetition for almost every machine, in last 5 days, which should be maximum 5, 1 per day — AKG
Check out my answer. It seems to work with your sample data. Of course you need to add another filter to get just the last 5 days. — Marco Vos

Marco Vos Marco Vos · Accepted Answer · 2020-03-12T09:06:57

Try DISTINCTCOUNT('SCCM Antivirus_RBF'[Date]) in stead of COUNT('SCCM Antivirus_RBF'[MachineName])

Something like this:

Repetition =
CALCULATE (
    DISTINCTCOUNT ( 'SCCM Antivirus_RBF'[Date] ),
    ALLEXCEPT ( 'SCCM Antivirus_RBF', 'SCCM Antivirus_RBF'[MachineName] ),
    'SCCM Antivirus_RBF'[Status] = "Non-Compliant"
)

Count the repetition of values in last 5 days in DAX

1 Answers