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