2 Replies Latest reply on Mar 7, 2018 2:07 PM by Manuel Velasquez

# Count number of Members in Criteria 2

Hi, I got the raw data below. They are members who joined training season in season 1, 2 and 3.

How do I come up with the table on the right? Thanks!

• ###### 1. Re: Count number of Members in Criteria 2

I have a way to do this. I am not saying it is a good way...

1) Make season a dimension.

2) Create a calculated field "Count Season 1/2"

COUNTD(if {fixed [Mbr ID]:max(if [Season] = 1 then 1 else 0 end)}

+ {fixed [Mbr ID]:max(if [Season] = 2 then 1 else 0 end)} = 2

THEN [Mbr ID]

END)

3) Create a calculated field "Count Season 2/3"

COUNTD(if {fixed [Mbr ID]:max(if [Season] = 2 then 1 else 0 end)}

+ {fixed [Mbr ID]:max(if [Season] = 3 then 1 else 0 end)} = 2

THEN [Mbr ID]

END)

4) Drag Measure names to rows, Measure Values to Columns.

5) Make the AGG()s under measure values continuous by right clicking.

I hope I didn't miss a step...

• ###### 2. Re: Count number of Members in Criteria 2

Hi Yichuan

One way to get the result you are looking is by using Sets and some calculations

First, create 3 set, each set should the "Mbr ID" that participated in each season.

You should have  3 set that represents the season

Create a calculation

Create the view using the new calculated field "Season Group" and COUNTD(Mbr ID), Exclude nulls.

Attached .twbx for reference

Let me know if this helps!

Manuel Velasquez