Forum Discussion

troystyle's avatar
troystyle
Regular Visitor
10 years ago

LOOKS Dax Measures Calculation HELP!

Hello All,

Help needed in resolving this dax measures calculation with regards to a report i am working on. f ind below a sample dataset.

The goal is to create two measures or columns. Further details are as follows:

  • Status ID and course are to be related in the measures
  • First qtr of enrollment signifies the first record of the status i.e The first time any particular Status ID was entered into the database
  • Census Term signifies the quarter the data was captured, thus there was data capture in 09/FA, 10/FA etc
  • Some status ID might not be present in subsequent quarters
  • Course also changes for some Status ID, Which means some status ID changes courses.

The general idea is to calculate retention numbers and percentage i.e how many status ID/ course are present as the years goes by.

 

Calculation1: Measure that looks through the dataset and looks for each status ID/course relationship and calculate the count of occurence of all the status ID registered for Politics as the census term changes. Note even if the course changes in Census Term 10/FA, The status ID will still be classified as part of the course count for 09/FA and so on..

 

 

Status IDCourseFirst Qtr of EnrollmentCensus Term
1694Politics09/FA09/FA
2095Arts09/FA09/FA
2444Politics09/FA09/FA
2758Science09/FA09/FA
3597Politics09/FA09/FA
4761Politics09/FA09/FA
6329Politics09/FA09/FA
7191Politics09/FA09/FA
8099Politics09/FA09/FA
2095Politics09/FA10/FA
2444Politics09/FA10/FA
2758Politics09/FA10/FA
3597Politics09/FA10/FA
4761Politics09/FA10/FA
6329Politics09/FA10/FA
7191Politics09/FA10/FA
8099Science09/FA10/FA
2758Science09/FA11/FA
3597Politics09/FA11/FA
4761Politics09/FA11/FA
6329Politics09/FA11/FA
7191Politics09/FA11/FA
8099Politics09/FA11/FA

 

 


Expected results/answer for calculation 1

Politics 
  
Census Term09/FA First Qtr of Enrollment Group
09/FA7
10/FA7
11/FA5

 

 

Highly appreciative of any help

Thanks

 

 

 

5 Replies

  • Sean's avatar
    Sean
    Icon for Community Champion rankCommunity Champion
    10 years ago

    troystyle let me know if this works...

     

    Measure 1A =
CALCULATE (
    COUNTROWS ( 'Table' ),
    ALLEXCEPT ( 'Table', 'Table'[Census Term], 'Table'[Program] )
)

     

    • troystyle's avatar
      troystyle
      Regular Visitor
      10 years ago

      SeanSadly it is not working or what i seek.

      The solution must be able to work on a Status ID/Program relationship i.e The count of Status ID studying politics in each quarter. the relationship is tied to Status ID/Program and first term. it must recognise the first term for it to work effectively.

       

      • Sean's avatar
        Sean
        Icon for Community Champion rankCommunity Champion
        10 years ago

        troystyle This shows count of Status ID studying politcis in each quarter?

        Do you need DISTINCT count?