Forum Discussion
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
Table
Table where CountYes > 1 for PartNumber
Table 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.
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 :
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
4 Replies
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
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 C Jan Yes C Jan No C Jan No C Jan No C Jan No C Feb Yes C Feb No C Feb Yes C Feb Yes C Feb Yes D Jan Yes D Jan Yes D Jan Yes D Feb No D Feb No D Feb No - Ritaf1983
Super User
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 :
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"))
