cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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!

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

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.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 419 members 4,242 guests
Please welcome our newest community members: