Forum Discussion

makerbob's avatar
makerbob
New Member
3 years ago
Solved

Use selected rows in new measure

I've been struggling with this in DAX for a day or two and cannot figure out what the best approach is. We've previously been using QLik Sense and we were able to create his report in there with similar data setup. Here's a simplified description of our challenge:

 

We have a table full of Persons and events attended, each Person has a unique ID but they can appear mutliple times in the table, but for different events. In that sense the rows are all unique.

 

In a report I want to show which people, and the count of them, attended event number 1. Alongside it I want to show which of those same people also attended event number 2. I can count number of attendees per event, but how can I count attendees for event # 2 and ONLY inlcude people who have been at event 1?

 

Example source table:

IDAttendee nameEvent #
516John1
65Sara1
816Mike1
906Mohammad1
597Jussi2
101Jack1
28Nelly1
310Violet2
516John2
65Sara2
816Mike2
906Mohammad2
555Elliot2

 

Who attended event 1:

IDAttendee nameEvent #
516John1
65Sara1
816Mike1
906Mohammad1
28Nelly1
101Jack1

 

And the result I'm looking for as the final step is this (people attending to event 2 who have been at event 1):

IDAttendee nameEvent #
906Mohammad2
816Mike2
65Sara2
516John2

 

What is the recommended approach to solve this kind of thing in DAX?

Need Help
  • TomasAndersson's avatar
    TomasAndersson
    3 years ago

    Hi!
    Not sure if you wanted this in separate measures, but this is one way that might work:
    First two separate measures to count who attendet event #1 and #2 respectively:

    Attended 1 = 
CALCULATE(
    COUNT('Table'[ID]),
    'Table'[Event #] = 1
)

Attended 2 = 
CALCULATE(
    COUNT('Table'[ID]),
    'Table'[Event #] = 2
)

     

    Then a third measure to see what IDs have attended both 1 and 2: 

    Attended both 1 and 2 = 
CALCULATE(
    DISTINCTCOUNT('Table'[ID]), //DISTINCT needed so as not to count IDs twice (once for event 1 and 2 each)
        FILTER(ALLSELECTED('Table'[ID]), [Attended 1] = 1 && [Attended 2] = 1
    )
)

     See result below:

    Hope this helps!

2 Replies

  • TomasAndersson's avatar
    TomasAndersson
    Icon for Solution Sage rankSolution Sage
    3 years ago

    Hi!
    Not sure if you wanted this in separate measures, but this is one way that might work:
    First two separate measures to count who attendet event #1 and #2 respectively:

    Attended 1 = 
CALCULATE(
    COUNT('Table'[ID]),
    'Table'[Event #] = 1
)

Attended 2 = 
CALCULATE(
    COUNT('Table'[ID]),
    'Table'[Event #] = 2
)

     

    Then a third measure to see what IDs have attended both 1 and 2: 

    Attended both 1 and 2 = 
CALCULATE(
    DISTINCTCOUNT('Table'[ID]), //DISTINCT needed so as not to count IDs twice (once for event 1 and 2 each)
        FILTER(ALLSELECTED('Table'[ID]), [Attended 1] = 1 && [Attended 2] = 1
    )
)

     See result below:

    Hope this helps!

  • Mahesh0016's avatar
    Mahesh0016
    Icon for Super User rankSuper User
    3 years ago

    makerbob If this post helps, please consider accept as solution to help other members find it more quickly.