Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Count Items with two conditions

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
Need Help
  • AlB's avatar
    AlB
    7 years ago

    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

4 Replies

  • Geradav's avatar
    Geradav
    Icon for Responsive Resident rankResponsive Resident
    7 years ago

    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

    • AlB's avatar
      AlB
      Icon for Community Champion rankCommunity Champion
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago

        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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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