LOOKS Dax Measures Calculation HELP!

[troystyle]

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 ID	Course	First Qtr of Enrollment	Census Term
1694	Politics	09/FA	09/FA
2095	Arts	09/FA	09/FA
2444	Politics	09/FA	09/FA
2758	Science	09/FA	09/FA
3597	Politics	09/FA	09/FA
4761	Politics	09/FA	09/FA
6329	Politics	09/FA	09/FA
7191	Politics	09/FA	09/FA
8099	Politics	09/FA	09/FA
2095	Politics	09/FA	10/FA
2444	Politics	09/FA	10/FA
2758	Politics	09/FA	10/FA
3597	Politics	09/FA	10/FA
4761	Politics	09/FA	10/FA
6329	Politics	09/FA	10/FA
7191	Politics	09/FA	10/FA
8099	Science	09/FA	10/FA
2758	Science	09/FA	11/FA
3597	Politics	09/FA	11/FA
4761	Politics	09/FA	11/FA
6329	Politics	09/FA	11/FA
7191	Politics	09/FA	11/FA
8099	Politics	09/FA	11/FA

Expected results/answer for calculation 1

Politics
Census Term	09/FA First Qtr of Enrollment Group
09/FA	7
10/FA	7
11/FA	5

Highly appreciative of any help

Thanks

[Sean]

@troystyle let me know if this works...

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

[troystyle]

@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]

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

Do you need DISTINCT count?

[troystyle]

@Sean The solution is close but not quite.

This is a sample created in excel out of a larger dataset of what i want to achieve. it must allow for filtering 

The result must show the actual count and the retention rate, this might require more than one dax measure to achieve as shown.

Thus status ID of Politics in 10/FA but changed to Arts in 11/FA, 12/FA, 13/FA will be counted as part of 10/FA Politics due to the fact that the status id registered as politics in its first year i.e 10/FA.

It seems convoluted but its the nature of my project and really do appreciate your help 

[Sean]

http://community.powerbi.com/t5/Desktop/Calculate-Difference-from-Previous-Date-Dynamically/m-p/3427...