cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ronkochanowski Frequent Visitor
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?

 

Thanks in advance!

Ron

1 ACCEPTED SOLUTION

Accepted Solutions
ronkochanowski Frequent Visitor
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
Moderator 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?

 

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" )
)
Super User
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
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!