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

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.