cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

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.

1 ACCEPTED SOLUTION
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

4 REPLIES 4
New Member

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

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

New Member

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

Super User

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?

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors