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.

FreemanZ's avatar
FreemanZ
Icon for Super User rankSuper 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