Forum Discussion

ReutAtias12's avatar
ReutAtias12
Frequent Visitor
3 years ago
Solved

count with condition

Hi All, 

 this is my data sample:

Account idattackidattack datemoduleassessNamestatus
1011/1/22aaxxfailed
1022/1/22aayyfailed
1032/1/22ccxxfailed
1043/1/22aaxxcompleted
2051/1/22ttssfailed
2062/1/22ttssfailed
2072/1/22aahhhfailed
20810/1/22aahhhfailed
3091/1/22rrppfailed
3102/1/22aammfailed 
3118/1/22rrppfailed

i'm trying to disply a table that will give me all account that runs 2 failed attakcs in row

the attack must to be in a row and must to have same assessName and same module name 

 

for example account 2 had 2 failed attacks in a row with the same module = tt and the same assessName = ss

acount can answer this conditions correctly more then 1 time,

for example account 2 add another 2 failed attacks in a row with the same module = aa and the same assessName = hhh

 

i want to disply on the report a table with all the attacks that answer these conditions 

idLast attack datemoduleassessName
22/1/22ttss
210/1/22aahhh

 

and then count the number of attacks that  answer these conditions (count shold be 2 base on the sample data)

 

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

 

pls help 🙂 

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.

8 Replies

  • daXtreme's avatar
    daXtreme
    Solution Sage
    3 years ago

    The description is not clear. If you want a good solution, you have to provide a good and comprehensible description of the problem.

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    Supposing your table named Data, try to create a new table with this code:

    Table =
    VAR Table1=
    SUMMARIZE(
        Data,
        Data[Account ID],
        Data[module],
        Data[assessName],
        Data[status]
    )
    VAR Table2=
    ADDCOLUMNS(
        Table1,    
        "Count",
        CALCULATE (COUNT (Data[attack date])),
        "Last attack",
        CALCULATE ( MAX( Data[attack date]))
    )
    VAR Table3 =
    FILTER(
      Table2,  
        [Count]>1
    )
    VAR Table4 =
        SELECTCOLUMNS(
            Table3,
            "ID", Data[Account ID],
            "Last attack", [Last attack],
            "module", Data[module],
            "assessName", Data[assessName]
        )
       
    RETURN Table4

     

    but how to reflect failed in a row? 😂

    • ReutAtias12's avatar
      ReutAtias12
      Frequent Visitor
      3 years ago

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

      ** user 3 not supposed to be in the result becuse in the middel of the attack 09 and 11 

      you have attack 10 with diffrent assessName and Module 

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