Count Number of rows based on status

[Anonymous]

Hi All, 

I need help with the following question: 

I have the following data 

Name	status1	status2
A	1	0
A	0	1
B	0	0
B	0	0
B	1	0
C	0	1
C	0	0

I need to write a DAX query where it should count such rows whose status1 = 1 and status2 was never 1

From the above example, we should count as 1. 

because B is the only entry for which status1= 1 and status2 never became 1. 

I can not create a table as I need to carry out this operation on a real-time dataset. 

Thanks, 

Vaibhav 

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create a measure as below.

Count = 
var tab = 
ADDCOLUMNS(
    'Table',
    "Flag",
    var _name = [Name]
    var _sts1 = [status1]
    var _sts2 = [status2]
    return
    IF(
        [status1]=1&&
        COUNTROWS(
                FILTER(
                    ALL('Table'),
                    'Table'[Name]=_name&&
                    'Table'[status2]=1
                )
        )=BLANK()
        ,
        1,0
    )
)
return
COUNTROWS(
    FILTER(
        tab,
        [Flag]=1
    )
)

Result:

Best Regards

Allan

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

[parry2k]

@Anonymous you can add the following column and then sum on this column to get the count

Col = 
VAR __totalRowsStatus2 = CALCULATE ( SUM ( 'status'[status2] ), ALLEXCEPT ( 'status', 'status'[Name] ) )
RETURN
IF ( 'status'[status1] = 1  && __totalRowsStatus2 = 0, 1 , 0 )

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[Anonymous]

@parry2k : Thanks for your message. 

We need to count row only when status1 = 1 and status2 <> 1 in all of there entries as rows.

[parry2k]

@Anonymous not sure, solution I posted is based on your original post, did you tested it? I'm not sure what is your last reply.

[Anonymous]

@parry2k : sorry for it but I do not have the option to add a column to the data table. Can this be carried out on the temp table?

Please guide me as I am fairly new to this world.

[ryan_mayu]

@Anonymous 

maybe try this measure

Measure = CALCULATE(COUNTROWS('Table (3)'),FILTER('Table (3)','Table (3)'[status1]=1&&'Table (3)'[status2]<>1))

However, i am wondering why you didn't count the first row? becuase that A is 1 for status 1 and 0 for status 2 as well.