Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
troystyle
Regular Visitor

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 5
Sean
Community Champion
Community Champion

@troystyle let me know if this works...

 

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

 

Measures2.png

@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
Community Champion
Community Champion

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

Do you need DISTINCT count?

Measures3.png

@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 

Capture.JPG

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 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.