cancel
Showing results for
Did you mean:
ronkochanowski 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
ronkochanowski 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.

Thanks for your input!

3 REPLIES 3 Eric_Zhang
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.

ronkochanowski 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.

Thanks for your input!

Announcements Top Kudoed Authors
Users Online
Currently online: 419 members 4,242 guests
Recent signins:
• irac • rtwalts • qwaiti27231 • kapilagurua • Massig • Prometheus_2 • drum1010 • dev_severance • afarrar • angelom • martazebrowska • deephdesai09 • Vegarck • ibenbuustricker 