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

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