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] ) )
    )
    )

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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi tamerj1 

    Just have one quick question I need help on with the formula above. While the formula works as intended, I am having one slight issue with the duplicates being omitted from the sum. For example:

    This is correct if I am trying to break this down by a weekly count.

    When I layer month in, it once again works correctly, taking the 8 out of January in a week that stretches into two months

     But when I condense it to show the month only, I get a smaller number: 

    By doing the quick math it should be 293, but because of the duplicate PREM_NB, there are 55 that are omitted in the 'sum' by month.

     

    Can you help on this? I would think it's an easy fix in the formula, but I am unsure...

    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      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] ) )
      )
      )