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 ?

    • v-zhangti's avatar
      v-zhangti
      Icon for Community Support rankCommunity Support
      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.

      • ReutAtias12's avatar
        ReutAtias12
        Frequent Visitor
        3 years ago

        first of all thank you!!

        one more thing,

        if the attack id and the account id are type text (can include text and number as an id) 

        how eill that change the maesures?