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

    Count number of Members in Criteria 2

    Yichuan Yu

      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!

      Capture.PNG

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

          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
            Manuel Velasquez

            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.

            1.PNG

            You should have  3 set that represents the season

             

            2.PNG

             

            Create a calculation

            3.PNG

             

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

             

            4.PNG

             

             

             

            Attached .twbx for reference

             

            Let me know if this helps!

             

            Manuel Velasquez