cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Laurence Frequent Visitor
Frequent Visitor

DAX counting statuses at certain date

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:

 

PBIRelationship.png 

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_idlog_idapp_statuslog_date
11applied01/01/2018
22applied05/01/2018
33applied05/01/2018
44applied08/01/2018
15accepted09/01/2018
56applied11/01/2018
47rejected16/01/2018
68applied16/01/2018
29withdrawn20/01/2018
610accepted23/01/2018
511accepted24/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_idnameyear_of_entryyear_groupcategorydate_first_appliedLatest_status
1Tom Smith2019711+01/01/2018accepted
2Sam Jones2019711+05/01/2018withdrawn
3Ed Example20201216+05/01/2018applied
4Sally Sample2020711+08/01/2018rejected
5Dan Demo2019711+11/01/2018accepted
6Jo Bloggs20201216+16/01/2018accepted

 

Between 03/01/2018 and 12/01/2018, there would be 4 applications, all ‘applied’ as of 12/01/2018:

application_idnameyear_of_entryyear_groupcategorydate_first_appliedLatest_satus
2Sam Jones2019711+05/01/2018applied
3Ed Example20201216+05/01/2018applied
4Sally Sample2020711+08/01/2018applied
5Dan Demo2019711+11/01/2018applied

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: DAX counting statuses at certain date

Hi @Laurence

Change the relationships as below

6.png

 

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)

 

5.png

 

Add [flag] in the Visual Level Filter, set "show items when value is 1"

7.png

 

Best Regards

Maggie

View solution in original post

3 REPLIES 3
LivioLanzo Super Contributor
Super Contributor

Re: DAX counting statuses at certain date

Hi @Laurence

 

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:

 

Capture.PNGCapture3.PNG

 

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

Capture.PNG

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Community Support Team
Community Support Team

Re: DAX counting statuses at certain date

Hi @Laurence

Change the relationships as below

6.png

 

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)

 

5.png

 

Add [flag] in the Visual Level Filter, set "show items when value is 1"

7.png

 

Best Regards

Maggie

View solution in original post

Laurence Frequent Visitor
Frequent Visitor

Re: DAX counting statuses at certain date

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:

PBIExampleFinal.png

 

Now I just have to translate this into the real model!

Thanks again,

Laurence

 

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)