Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Achieve Distinct Count with Slicer

I have a table of student data which shows various changes to application records, including when an offer has been made.  The dates are linked to a day of year number, and the rows are indexed for each applicant based on the application rank field.

 

StudentIdApplicationRankDayNumberOfferLatestRecord
StudentA1200FALSE
StudentA2350FALSE
StudentA3470FALSE
StudentA42001FALSE
StudentA52201FALSE
StudentA62201TRUE
StudentA72391FALSE
StudentB12000FALSE
StudentB22011TRUE
StudentB33001FALSE
StudentB43011FALSE

 

The "LatestRecord" field is a calculated column as follows, where CurrentDayOfYearNumber returns the day number based on today's date:

 
LatestRecord =

IF(
[ApplicationRank] <> BLANK()
&& 'StudentData'[ApplicationRank] =
MAXX(
FILTER(
'StudentData',
[StudentId] = EARLIER( [StudentId])
&& 'StudentData'[DayNumber]<= [CurrentDayOfYearNumber]
),
'StudentData'[ApplicationRank]
),
TRUE(), FALSE()
)
 
So in the table the day number is at 220, therefore the result is TRUE for StudentA ApplicationRank 6 and for StudentB ApplicationRank 2.  I can then use this field to always identify the row to be calculated in the measure which counts the number of offers, so on day 220 offers equals 2 (using a distinct count on StudentId and LatestRecord = TRUE).
 
I now want to use a DayNumber slicer to dynamically move through the days and show the number of offers at each point, however even with distinct count I can't get a count of an individual row without the context of something similar to LatestRecord, so for day 220 the offer count ends up being 4 (number of rows where offer = 1 and day number is <= day 220 in slicer).
 
Any help much appreciated!
Need Help

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous 

    I suggest you to create an unrelated DayNumber Table by dax or what if parameter to create a slicer. Then calculate the distinct count of student ID by measure.

    DayNumber Table:

    DayNumber = GENERATESERIES(1,MAX('Table'[DayNumber]))

    Measure:

    Distinct Count ID = 
VAR _SelectDayNumber =
    SELECTEDVALUE ( DayNumber[Value] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[StudentId] ),
        FILTER ( ALL ( 'Table' ), 'Table'[DayNumber] <= _SelectDayNumber )
    )

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      This has worked perfectly to get the count using a What If Parameter and the following measure:

       

      Count =

      VAR SelectedDay = SELECTEDVALUE( DateSelect[DateSelect])

      RETURN

      CALCULATE(

       DISTINCTCOUNT(

           'Table'[StudentId]

       ),

       'Table'[Offer] = "1",

       FILTER(

           ALLSELECTED(

               'Table'

           ),

      'Table'[DayNumber] <= SelectedDay

       && 'Table'[ApplicationRank] <= MAX( 'Table'[ApplicationRank]

       )

      )

      )

       

      but I have an extra layer of complexity which it doesn't seem to solve - I've added the OfferType columns below to explain this:

       

      StudentId

      ApplicationRank

      DayNumber

      Offer

      OfferTypeA

      OfferTypeB

      LatestRecord

      StudentA

      1

      20

      0

      0

      0

      FALSE

      StudentA

      2

      35

      0

      0

      0

      FALSE

      StudentA

      3

      47

      0

      0

      0

      FALSE

      StudentA

      4

      200

      1

      1

      0

      FALSE

      StudentA

      5

      220

      1

      1

      0

      FALSE

      StudentA

      6

      220

      1

      0

      1

      TRUE

      StudentA

      7

      239

      1

      0

      1

      FALSE

      StudentB

      1

      200

      0

      0

      0

      FALSE

      StudentB

      2

      201

      1

      1

      0

      TRUE

      StudentB

      3

      300

      1

      1

      0

      FALSE

      StudentB

      4

      301

      1

      0

      1

      FALSE

       

      So I would need the count as follows:

      DayNumber 200 – Offer = 1; OfferTypeA = 1

      DayNumber = 201 – Offer = 2; OfferTypeA = 2

      DayNumber = 220 – Offer = 2; OfferTypeA = 1; OfferTypeB = 1

      So by day number 301 Offer = 2; OfferTypeB = 2

       

      The problem at the moment is that the count is including all those which have ever been an OfferTypeA even if they then switch to OfferTypeB, so DayNumber 301 Offer = 2; OfferTypeA = 2; OfferTypeB = 2

       

      Is there any way to fix this within my measure?

       

      Many thanks

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    You should be able to implement this with a "What-If"  parameter and adjusting your measure accordingly.

     

    What happens when this process extends beyond one year? Or starts before Jan 1st?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      There are further steps which ensure that all activity remains within a one year window, which is an academic year so runs from October each year, so this wouldn't cause an issue with the slicer - it should work across all years.

      Can you elaborate on what you mean by  "a "What-If"  parameter and adjusting your measure accordingly " please?

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hello crains 
    Have you tried changing the condition to be based on selected date slicer?

    And adding filter for all values to filtter based on max selected date value

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      I can't seem to make a calculated column within the table which identifies the record to be selected based on the value in the slicer - is this possible?

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Anonymous Hello,

         

        You cannot create calculated column that changes dynamically, But you can create measure that uses selected value formula or in this case I would recomend using Max(date slicer value)