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 I...
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

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