Forum Discussion

ZeeMTee's avatar
ZeeMTee
New Member
3 years ago
Solved

Need help with DAX for count

Hello DAX master, I am currently stuck.  I have this table, with 3 columns PartNumber, Month and Changed.  I want to count Changed = Yes, for each PartNumber and display the results in a visual wh...
Need Help
  • Ritaf1983's avatar
    Ritaf1983
    3 years ago

    Hi ZeeMTee 
    You can add a flag column, which you can use as a filter.
    It returns "yes" if the same part in the same month has more than 1 yes.
    Dax formula for this calculated column :

    CountYes = if ( CALCULATE(COUNTROWS('Table'),
    ALLEXCEPT('Table', 'Table'[Pnumber], 'Table'[Month]),
     'Table'[Changed] = "Yes")>1, "yes", "blank")

    As you have it you can use it to filter your matrix :

    Link to a sample file 

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

ZeeMTee's avatar
ZeeMTee
New Member
3 years ago

Hi, 

Thanks for assisting.

 

If I do  a Pivot of the data and filter for Changed = Yes. I will get this result. 

The cells in yellow should be discard as the count of Changed < 1.

 

From this table I would do COUNTA for respective months, and would get this table

 

The final table should look like this

 

Here is the sample data

PartNumberMonthChanged
AJanYes
AJanYes
AJanYes
AFebYes
AFebNo
AFebYes
AFebYes
AFebNo
BJanYes
BJanYes
BFebNo
BFebYes
BFebNo
CJanYes
CJanNo
CJanNo
CJanNo
CJanNo
CFebYes
CFebNo
CFebYes
CFebYes
CFebYes
DJanYes
DJanYes
DJanYes
DFebNo
DFebNo
DFebNo

 

 

  • Ritaf1983's avatar
    Ritaf1983
    Icon for Super User rankSuper User
    3 years ago

    Hi ZeeMTee 
    You can add a flag column, which you can use as a filter.
    It returns "yes" if the same part in the same month has more than 1 yes.
    Dax formula for this calculated column :

    CountYes = if ( CALCULATE(COUNTROWS('Table'),
    ALLEXCEPT('Table', 'Table'[Pnumber], 'Table'[Month]),
     'Table'[Changed] = "Yes")>1, "yes", "blank")

    As you have it you can use it to filter your matrix :

    Link to a sample file 

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

    • ZeeMTee's avatar
      ZeeMTee
      New Member
      3 years ago

      Thanks I have added another measure to CountOfYes = "Yes", so that I can get the PartNumber count.

       

      CountIFYes = CALCULATE( DISTINCTCOUNT('Table'[PartNumber]), Filter('Table', 'Table'[CountOfYes]="Yes"))