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

v-yinliw-msft's avatar
v-yinliw-msft
Icon for Community Support rankCommunity Support
3 years ago

Hi Anonymous ,

 

I'm not sure I understood your question correctly, you mean in the above figure, the 045485994 and 045612820 rows don't want to be displayed in the matrix?

And could you please give me some sample data to test? Please delete sensitive and private data.

 

Best Regards,

Community Support Team _Yinliw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
3 years ago

I cannot send data on this, sorry. I can try to explain better. If the chart below was an Excel Pivot Table, I would need to filter out the counts of DN and SC, thus eliminating the UC as well. These are duplicate Prem_NB. Although I need all the data, so I cannot remove duplicates either using Power Query. I just do not want the duplicate PREM_NB to show up, so I would be left with a count of 12 in UC.

So while this formula gets me about halfway there, I need a filter(?) formula to take out the counts of the other action codes (DN/SC) and also the UC if they have one. The duplicate prem_nb are 042016185, 045485994, 045612820.

 

CALCULATE(
    COUNTA('DataSet'[CRDT_COLL_ACTN_CD]),
    'DataSet'[CRDT_COLL_ACTN_CD] IN { "UC" }
)