Count with Multiple Criteria

[GJUDGE]

I'm sorry if similar questions have been asked before but I am trying to count rows based on different criteria:

Project	Status 1	Status 2	Status 3
A	1	400	200
B	2	-200	-100
C	3	200	-100

I am trying to work out a formula which will show the following:

Count If Status 1 = 1 and Status 2 and Status 3 are both positive values, return "all status ok"

Count If Status 1 = 2 or 3 and either status 2 or 3 have one value positive and one value negative, return "some status ok"

Count If status 1 = 3 and both status 2 and 3 are negative, return "no status ok".

Thanks in advance.

[v-xulin-mstf · Accepted solution]

Hi @GJUDGE

Is this your expected output?

Create measures as:

all status ok = 
CALCULATE(
    COUNTROWS('Table 1'),
    FILTER(
        'Table 1',
        'Table 1'[Status 1]=1 && 'Table 1'[Status 2]>0 && 'Table 1'[Status 3]>0
    ))

some status ok = 
CALCULATE(
    COUNTROWS('Table 1'),
    FILTER(
        'Table 1',
        'Table 1'[Status 1] in {2,3} && 'Table 1'[Status 2] * 'Table 1'[Status 3] <0
    ))

no status ok = 
CALCULATE(
    COUNTROWS('Table 1'),
    FILTER(
        'Table 1',
        'Table 1'[Status 1]=3 && 'Table 1'[Status 2]<0 && 'Table 1'[Status 3]<0
    ))

If you still have some question, please don't hesitate to let me known.‌‌

Best Regards,

Link

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

[Ashish_Mathur]

Hi,

Try this calculated column formula

=if(Data[Status1]=3&&Data[Status2]<0&&Data[Status]3<0,"No status OK",if(Data[Status1]=1,if(and(Data[Status2]>0,Data[Status3]>0),"All Status OK","Blank()),sign(Data[Status2]*Data[Status3])=-1,"some status OK"))