cancel
Showing results for
Did you mean:
Frequent Visitor

## Count Rows Where ID Matches Other Measure in Same Table

Have a dimension that contains one column that is one of four values: STEP 1, STEP 2, STEP 3, STEP 4.  STEP 1 is not a prerequisite, but is a starting point.  In other words, people might jump to STEP 2 or 3 without doing STEP 1.  I need to find all people that have performed STEPs 2, 3, and 4, but only if they've first done STEP 1.

Currently have measures:

STEP 1 = CALCULATE(COUNTROWS(myTable),myTable[step]="Step1")

STEP 2 = CALCULATE(COUNTROWS(myTable),myTable[step]="Step2")

STEP 3 = CALCULATE(COUNTROWS(myTable),myTable[step]="Step3")

STEP 4 = CALCULATE(COUNTROWS(myTable),myTable[step]="Step4")

These measures give the totals without an issue.  The field that joins the steps is Person_ID.  I'm thinking something like, COUNTIF STEP2 PERSON_ID = STEP1 PERSON_ID... I know this is a very simplistic way of thinking about it, but it's what I'm looking for.  How would that be derived in DAX?

Ron

1 ACCEPTED SOLUTION

Accepted Solutions
Frequent Visitor

## Re: Count Rows Where ID Matches Other Measure in Same Table

Thanks Eric,

This is what has seemed to work:

`To Community = CALCULATE(COUNTROWS(core_v_profile_memberList),FILTER(ALL(core_v_profile_memberList[Step]),core_v_profile_memberList[Step] = "Community"))`

with Community being Step 4.

3 REPLIES 3
Moderator

## Re: Count Rows Where ID Matches Other Measure in Same Table

@ronkochanowski wrote:

Have a dimension that contains one column that is one of four values: STEP 1, STEP 2, STEP 3, STEP 4.  STEP 1 is not a prerequisite, but is a starting point.  In other words, people might jump to STEP 2 or 3 without doing STEP 1.  I need to find all people that have performed STEPs 2, 3, and 4, but only if they've first done STEP 1.

Currently have measures:

STEP 1 = CALCULATE(COUNTROWS(myTable),myTable[step]="Step1")

STEP 2 = CALCULATE(COUNTROWS(myTable),myTable[step]="Step2")

STEP 3 = CALCULATE(COUNTROWS(myTable),myTable[step]="Step3")

STEP 4 = CALCULATE(COUNTROWS(myTable),myTable[step]="Step4")

These measures give the totals without an issue.  The field that joins the steps is Person_ID.  I'm thinking something like, COUNTIF STEP2 PERSON_ID = STEP1 PERSON_ID... I know this is a very simplistic way of thinking about it, but it's what I'm looking for.  How would that be derived in DAX?

Ron

@ronkochanowski

You can add an extra filter to your measure. Apply the same filter to Step 3 and Step 4.

```STEP 2 =
CALCULATE (
COUNTROWS ( myTable ),
myTable[step] = "Step2",
MyTable[Person_ID]
IN CALCULATETABLE ( VALUES ( MyTable[Person_ID] ), MyTable[Step] = "step1" )
)```
Super User

## Re: Count Rows Where ID Matches Other Measure in Same Table

Hi,

Share a dataset and show the expected result.

Frequent Visitor

## Re: Count Rows Where ID Matches Other Measure in Same Table

Thanks Eric,

This is what has seemed to work:

`To Community = CALCULATE(COUNTROWS(core_v_profile_memberList),FILTER(ALL(core_v_profile_memberList[Step]),core_v_profile_memberList[Step] = "Community"))`

with Community being Step 4.

Announcements

#### Community News & Announcements

Get your latest community news and announcements.

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 419 members 4,242 guests
Recent signins: