Forum Discussion

ReutAtias12's avatar
ReutAtias12
Frequent Visitor
3 years ago
Solved

count with condition

Hi All,   this is my data sample: Account id attackid attack date module assessName status 1 01 1/1/22 aa xx failed 1 02 2/1/22 aa yy failed 1 03 2/1/22 cc xx failed...
count of
DAX
Power BI Dax
  • v-zhangti's avatar
    v-zhangti
    3 years ago

    Hi, ReutAtias12 

     

    Of course.

    Measure:

    Measure 1 = 
CALCULATE (
    COUNT ( 'Table'[Account id] ),
    FILTER (ALL('Table'),
        [assessName] = SELECTEDVALUE( 'Table'[assessName] )
            && [module] = SELECTEDVALUE( 'Table'[module] )
            && [status] = "failed"
            && [attackid] <= SELECTEDVALUE ( 'Table'[attackid] )
    )
)
    Measure 2 = 
VAR _Previous =
    MAXX (
        FILTER (
            ALL('Table'),
            [attackid] < SELECTEDVALUE( 'Table'[attackid] )
                && [module] = SELECTEDVALUE('Table'[module] )
                && [assessName] = SELECTEDVALUE( 'Table'[assessName] )
        ),
        [attackid]
    )
RETURN
    IF ( [Measure 1] = 2, _Previous, BLANK () )
    Measure 3 = IF(SUM('Table'[attackid])-[Measure 2]=1&&[Measure 2]<>BLANK(),1,BLANK())
    Count = CALCULATE(COUNT('Table'[Account id]),FILTER(ALL('Table'),[Measure 3]=1))

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

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

v-zhangti's avatar
v-zhangti
Icon for Community Support rankCommunity Support
3 years ago

Hi, ReutAtias12 

 

You can try the following methods.

Column:

runs 2 failed =
CALCULATE (
    COUNT ( 'Table'[Account id] ),
    FILTER (
        'Table',
        [assessName] = EARLIER ( 'Table'[assessName] )
            && [module] = EARLIER ( 'Table'[module] )
            && [status] = "failed"
            && [attackid] <= EARLIER ( 'Table'[attackid] )
    )
)

Previous attackid =
VAR _Previous =
    MAXX (
        FILTER (
            'Table',
            [attackid] < EARLIER ( 'Table'[attackid] )
                && [module] = EARLIER ( 'Table'[module] )
                && [assessName] = EARLIER ( 'Table'[assessName] )
        ),
        [attackid]
    )
RETURN
    IF ( [runs 2 failed] = 2, _Previous, BLANK () )

is consecutive = IF([attackid]-[Previous attackid]=1&&[Previous attackid]<>BLANK(),1,BLANK())

New table:

Table 2 = FILTER('Table',[is consecutive]=1)

Measure:

Count = CALCULATE(COUNT('Table'[Account id]),FILTER(ALL('Table'),[is consecutive]=1))

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

ReutAtias12's avatar
ReutAtias12
Frequent Visitor
3 years ago

can it be done only with maesure and not with calculated column ?