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

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