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" }
    )

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    PREM_NB ORDR_NB ACTN_CD
    040499503 049979553 UC
    041496276 044247981 UC
    041618911 043573555 UC
    041717966 042658832 UC
    042001243 042886126 UC
    042016185 048866316 DN
    042016185 041670231 UC
    042652002 041132464 UC
    043105966 043785616 UC
    043289966 045280766 UC
    044296966 043610958 UC
    044916627 047529882 UC
    044998403 042261670 UC
    045485994 041311258 UC
    045485994 043584388 SC
    045612820 046053949 UC
    045612820 049905916 SC
    048833971 043678617 UC

     

    Here is a mockup of data that could help. There are three duplicate PREM_NB that have separate order numbers. I would need those to not come through in the matrix in Power BI. Even though they have an ACTN_CD of UC, I would want those omitted. I think by doing the count on the ACTN_CD it's not working, although I cannot figure out my filter or anything.