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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
v-juanli-msft
Community Support
Community Support

Hi @Anonymous

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

5 REPLIES 5
hashim4626
New Member

Can you please share the pbix file with data sources

v-juanli-msft
Community Support
Community Support

Hi @Anonymous

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

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

Anonymous
Not applicable

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

 

LivioLanzo
Solution Sage
Solution Sage

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:

 

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!  

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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