Count Items with two conditions

[Anonymous]

Hi All,

I have a Sales Pipeline data table with three columns (Opportunity ID, Opportunity Line ID and P&C) - Each opportunity can have a single Opportunity Line ID or Multiple Opportunity Line IDs - each of these Opportunity Line Ids is either P&C or not (YES/NO in the data table) = what I am trying to do is create a filter that will only leave Opportunity IDs where all of the Opportunity Line IDs are YES in the P&C column. 

Opportunity ID	Opportunity Line ID	P&C
OP# 00287647	OL# 00394440	No
OP# 00290264	OL# 00411101	Yes
OP# 00291641	OL# 00412303	No
OP# 00388547	OL# 00520430	Yes
OP# 00415485	OL# 00482420	No
OP# 00425321	OL# 00443910	No
OP# 00425553	OL# 00460641	No
OP# 00428801	OL# 00458754	No
OP# 00430863	OL# 00450337	No
OP# 00556277	OL# 00652551	Yes
OP# 00556278	OL# 00652553	Yes
OP# 00557461	OL# 00654044	No
OP# 00560593	OL# 01421080	No
OP# 00617558	OL# 00722388	No
OP# 00664109	OL# 00770213	Yes
OP# 00665387	OL# 00771749	No
OP# 00665387	OL# 01434739	No
OP# 00667607	OL# 01245284	No
OP# 00687326	OL# 00806725	Yes
OP# 00687329	OL# 00806730	No
OP# 00687332	OL# 00806734	No
OP# 00687435	OL# 00806863	Yes
OP# 00687437	OL# 00806866	Yes
OP# 00687440	OL# 00806869	Yes
OP# 00698472	OL# 00831241	No
OP# 00704537	OL# 00838439	Yes
OP# 00704537	OL# 01007084	Yes
OP# 00707508	OL# 00841970	Yes
OP# 00709921	OL# 00845065	No

the above is a sample of my data. The Lines in Red are those which I want to get in my result. I have tried creating a column that counts the total number of Opportunity Line Ids:

Count Opportunity Line IDs = CALCULATE(

COUNT([Opportunity Line ID]),

FILTER('Pipeline', [Opportunity Line ID] = EARLIER('Pipeline'[Opportunity Line ID])))

and what I was trying to do is create another column that ccounts the number of YES values in the P&C column for each Opportunity ID - and then filter those where the Total Count of Opportunity Line IDs equals the Total Number of YES P&C values.

But I'm stuck.

I would be greatful for any/all help.

Thank you

Andrew

[Geradav]

Hi @Anonymous 

Try this DAX statement in a calculated column to identify all Yes P&C records

AllYes =
CALCULATE (
    COUNTROWS ( FILTER ( OpportunityTbl, OpportunityTbl[P&C] = "Yes" ) )
)

Let us know if that works for you

Regards

David

[Anonymous]

Hi David,

Thank you for your reply.

I added that column and it correctly counted the rows where P&C is Yes, but when I try to create a formula that checks whether the total number of Yes values equals the total number of rows in the opportunity I get a circular dependency error:

Count Opportunity Line IDs = CALCULATE(

COUNT([Opportunity Line ID]),

FILTER('2 Pipeline', [Opportunity Line ID] = EARLIER('2 Pipeline'[Opportunity Line ID])))

AllYes =

CALCULATE (

COUNTROWS ( FILTER ( '2 Pipeline', '2 Pipeline'[P&C] = "Yes" ) )

)

All P&C = '2 Pipeline'[AllYes] = '2 Pipeline'[Count Opportunity Line IDs] - this gives me the error

Thank you

[AlB · Accepted solution]

Hi @Anonymous 

You can create a new (filtered) table:

FilteredTable =
FILTER (
    Table1;
    VAR PC_Values_ =
        CALCULATETABLE (
            DISTINCT ( Table1[P&C] );
            ALLEXCEPT ( Table1; Table1[Opportunity ID] )
        )
    VAR AnyNoes_ = "No" IN PC_Values_
    RETURN
        NOT AnyNoes_
)

or create a measure with similar code and use it in a visual level filter

[Anonymous]

Thank you for your help - I used that formula to create a new calculated table and then filtered using that and it worked exactlr as I wanted. Thanks a lot.

Andrew