Count Rows Where ID Matches Other Measure in Same Table

[ronkochanowski]

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?

Thanks in advance!

Ron

[Ashish_Mathur]

Hi,

Share a dataset and show the expected result.

[Eric_Zhang]

---

@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?

Thanks in advance!

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

[ronkochanowski · Accepted solution]

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!