Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Need DAX help on Calculate, Count and Filter Functionality

I've tried an IF(AND and CALCULATE(COUNT but I am struggling to find how to exclude the possibility of some variables in a count formula.   I need to return everything in a Column (Action Code) tha...
CALCULATE
COUNT
DAX
formulas
  • tamerj1's avatar
    tamerj1
    3 years ago

     Anonymous 
    Not sure if I correctly understand your requirement. Hope this is what you need. See Attached sample file.

    Count = 
SUMX (
    VALUES ( 'DataSet'[PREM_NB] ),
    IF (
        COUNTROWS ( 
            CALCULATETABLE ( 
                VALUES ( 'DataSet'[CRDT_COLL_ACTN_CD] ), 
                ALL ( 'DataSet'[CRDT_COLL_ACTN_CD] ) 
            ) 
        ) <= 1,
        CALCULATE ( COUNTA('DataSet'[CRDT_COLL_ACTN_CD]) )
    )
)
  • tamerj1's avatar
    tamerj1
    3 years ago

    Anonymous 

    Please try

    Count =
    SUMX (
    SUMMARIZE ( 'DataSet', 'DataSet'[PREM_NB], 'Date'[Week] ),
    IF (
    COUNTROWS (
    CALCULATETABLE (
    VALUES ( 'DataSet'[CRDT_COLL_ACTN_CD] ),
    ALL ( 'DataSet'[CRDT_COLL_ACTN_CD] )
    )
    ) <= 1,
    CALCULATE ( COUNTA ( 'DataSet'[CRDT_COLL_ACTN_CD] ) )
    )
    )

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

Hi Anonymous 
Please use 

Count = 
CALCULATE(
    COUNTA ( 'DataSet'[CRDT_COLL_ACTN_CD] ),
    KEEPFILTERS ( 'DataSet'[CRDT_COLL_ACTN_CD] = "UC" )
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Thanks for the reply. Tried that one as well, but I need the filter to remove anything that has a count in the UC and SC & UC and DN as well. So this gets me halfway there, but not fully. I'm not sure how to write the code as a combination, even if there are duplicate PREM_NB.

    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      3 years ago

       Anonymous 
      Not sure if I correctly understand your requirement. Hope this is what you need. See Attached sample file.

      Count = 
SUMX (
    VALUES ( 'DataSet'[PREM_NB] ),
    IF (
        COUNTROWS ( 
            CALCULATETABLE ( 
                VALUES ( 'DataSet'[CRDT_COLL_ACTN_CD] ), 
                ALL ( 'DataSet'[CRDT_COLL_ACTN_CD] ) 
            ) 
        ) <= 1,
        CALCULATE ( COUNTA('DataSet'[CRDT_COLL_ACTN_CD]) )
    )
)
      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        This seems to have worked for me. Thank you so much!