Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
ZeeMTee
New Member

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 where it shows the for the month the count of PartNumber with count Changed = Yes is greater than 1.

 

PartNumber , Month , Changed

A , Jan , Yes

A , Jan , Yes

A , Jan , Yes

A , Feb , Yes

A , Feb , No

A , Feb , Yes

A , Feb , Yes

A , Feb , No

B , Jan , Yes

B , Jan , Yes

B , Feb , No

B , Feb , Yes

B , Feb , No

 

TableTable

 

Table where CountYes > 1 for PartNumber  

Table filter for CountYes > 1. Need to count for the month How PartNumber exceed CountYes > 1Table filter for CountYes > 1. Need to count for the month How PartNumber exceed CountYes > 1

 

The visual I need to create 

                                                                               Jan   Feb

Count of PartNumber with Count = Yes > 1     2      1

 

Any and all assistance is much appreciated.

 

 

 

 

1 ACCEPTED SOLUTION

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")
Ritaf1983_0-1686281444243.png

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

Ritaf1983_1-1686281562490.png

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

View solution in original post

4 REPLIES 4
ZeeMTee
New Member

Hi, 

Thanks for assisting.

 

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

IntermediateTable.PNG

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

IntermediateTable1.PNG

 

The final table should look like this

FinalTable1.PNG

 

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

 

 

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")
Ritaf1983_0-1686281444243.png

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

Ritaf1983_1-1686281562490.png

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

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"))
 
 
Solution.PNG
Ritaf1983
Super User
Super User

@ZeeMTee 

I'm sorry I don't understand what you're trying to do.
Could you please attach a picture of the desired result based on your sample data?

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.