Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
I am beginning to get my head around the basics of DAX but I have a problem that has me stumped.
I am working with student applications and need to create a measure that enables me to find how many applications were at each status at a given date.
Here is a simplified version of the tables and relationships:
Each application may go through several statuses during its life - applied, accepted, rejected, withdrawn. The history of the status, and date of change, are stored in the status log table, each application will have several rows but the latest one will be its ‘current status’ (note: it's possible for there to be multiple status changes for a single application in one day, so the one with the greater log_id would take precidence).
Here is some example data for each table (dates are dd/mm/yyyy):
App_main
application_id | name | year_of_entry | year_group | category | date_first_applied |
1 | Tom Smith | 2019 | 7 | 11+ | 01/01/2018 |
2 | Sam Jones | 2019 | 7 | 11+ | 05/01/2018 |
3 | Ed Example | 2020 | 12 | 16+ | 05/01/2018 |
4 | Sally Sample | 2020 | 7 | 11+ | 08/01/2018 |
5 | Dan Demo | 2019 | 7 | 11+ | 11/01/2018 |
6 | Jo Bloggs | 2020 | 12 | 16+ | 16/01/2018 |
App_status_log
application_id | log_id | app_status | log_date |
1 | 1 | applied | 01/01/2018 |
2 | 2 | applied | 05/01/2018 |
3 | 3 | applied | 05/01/2018 |
4 | 4 | applied | 08/01/2018 |
1 | 5 | accepted | 09/01/2018 |
5 | 6 | applied | 11/01/2018 |
4 | 7 | rejected | 16/01/2018 |
6 | 8 | applied | 16/01/2018 |
2 | 9 | withdrawn | 20/01/2018 |
6 | 10 | accepted | 23/01/2018 |
5 | 11 | accepted | 24/01/2018 |
In the final dashboard I would like to use slicers to filter the applications by category, year_of_entry, and date_first_applied using a date range. Then count the latest status, within the date range, for each application. I can then use this to work out the percentage of applications at each stage at a given date and compare them to the same period last year.
For example, using the data above the totals would be -
With no filters, there would be 6 applications with the following statuses accepted 3, applied 1, withdrawn 1 and rejected 1:
application_id | name | year_of_entry | year_group | category | date_first_applied | Latest_status |
1 | Tom Smith | 2019 | 7 | 11+ | 01/01/2018 | accepted |
2 | Sam Jones | 2019 | 7 | 11+ | 05/01/2018 | withdrawn |
3 | Ed Example | 2020 | 12 | 16+ | 05/01/2018 | applied |
4 | Sally Sample | 2020 | 7 | 11+ | 08/01/2018 | rejected |
5 | Dan Demo | 2019 | 7 | 11+ | 11/01/2018 | accepted |
6 | Jo Bloggs | 2020 | 12 | 16+ | 16/01/2018 | accepted |
Between 03/01/2018 and 12/01/2018, there would be 4 applications, all ‘applied’ as of 12/01/2018:
application_id | name | year_of_entry | year_group | category | date_first_applied | Latest_satus |
2 | Sam Jones | 2019 | 7 | 11+ | 05/01/2018 | applied |
3 | Ed Example | 2020 | 12 | 16+ | 05/01/2018 | applied |
4 | Sally Sample | 2020 | 7 | 11+ | 08/01/2018 | applied |
5 | Dan Demo | 2019 | 7 | 11+ | 11/01/2018 | applied |
I dont actually need to display the tables I just need the totals of each status taking into account the filters and date range.
Any help would be most appretiated!
Thanks,
Laurence
Solved! Go to Solution.
Hi @Anonymous
Change the relationships as below
create measures in "App_main" table
max_log_id = CALCULATE(MAX(App_status_log[log_id]),FILTER(ALLSELECTED(App_main),[application_id]=MAX([application_id]))) lastest_status = CALCULATE(MAX(App_status_log[app_status]),FILTER(ALLEXCEPT(App_main,App_main[application_id]),[max_log_id]=MAX(App_status_log[log_id]))) min_date = MIN('calendar'[Date]) max_date = MAX('calendar'[Date]) flag = IF(MAX([date_first_applied])<=[max_date]&&MAX([date_first_applied])>=[min_date],1,0)
Add [flag] in the Visual Level Filter, set "show items when value is 1"
Best Regards
Maggie
Can you please share the pbix file with data sources
Hi @Anonymous
Change the relationships as below
create measures in "App_main" table
max_log_id = CALCULATE(MAX(App_status_log[log_id]),FILTER(ALLSELECTED(App_main),[application_id]=MAX([application_id]))) lastest_status = CALCULATE(MAX(App_status_log[app_status]),FILTER(ALLEXCEPT(App_main,App_main[application_id]),[max_log_id]=MAX(App_status_log[log_id]))) min_date = MIN('calendar'[Date]) max_date = MAX('calendar'[Date]) flag = IF(MAX([date_first_applied])<=[max_date]&&MAX([date_first_applied])>=[min_date],1,0)
Add [flag] in the Visual Level Filter, set "show items when value is 1"
Best Regards
Maggie
@v-juanli-msft Hi, I hope you're doing great. I have similar problem here with a headcount and employee status. Is it possible to tell me on what are the connections for each table showed above ?
Thank you so much for your help Maggie - @v-juanli-msft
Your solution got me 99% of the way there. I did experience an issue with the latest_status measure when I began changing the date slicer. The max_log_id would return the correct id but the latest_status would not correlate (see Tom Smith in the screenshot below where the date has changed), so to get the latest status I added the following measure:
latest_status_v2 = LOOKUPVALUE(App_status_log[app_status],App_status_log[log_id],[max_log_id])
Then created the following to calculate the totals:
count_of_applied = calculate(countrows(App_main),filter(App_main,App_main[flag]=1&&App_main[latest_status_v2]="applied")) count_of_accepted = calculate(countrows(App_main),filter(App_main,App_main[flag]=1&&App_main[latest_status_v2]="accepted"))
It ended up like this:
Now I just have to translate this into the real model!
Thanks again,
Laurence
Hi @Anonymous
I think in your last screenshot, you've counted it wrong, as it should be 4 Applied and 1 Accepted or not?
You could build a model like the one below:
Then you could add this measure:
Count of Status = IF( HASONEVALUE( Statuses[Status] ), COUNTROWS( FILTER( VALUES( Applications[application_id] ), CALCULATE( CONCATENATEX( TOPN( 1, ApplicationsLog, ApplicationsLog[log_date], DESC ), ApplicationsLog[app_status] ) ) = VALUES( Statuses[Status] ) ) ) )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |