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
tanquoc0309
Helper II
Helper II

DAX help - Summarize based on the date and latest status

I have the data:

 

Capture.PNG

And I want to show the report:

 111.PNG

I need to know the last status (New String) of each issue until the date.

Ex: Issue A, changed status many times (30/3 changed to In Progress, 2/4 changed to Resolve, 4/4 change to Reopened, 10/4 changed to Closed.

So: 

The chart will show:
30/3 -> +1 ticket (A) for status In Progress
31/1 -> 1/4 -> +1 ticket (A) for status In Progress (Because A didn't change during that time) 

2/4 -> + 1 for Resolve, (No + for In Progress)...

I try 2 formulas but the performance is very slow. My idea is calculate for each status:

L_S_In Progress = 
VAR MaxDateView =
    CALCULATE ( LASTDATE ( DimDate[Date] ) )
RETURN
    CALCULATE (
        COUNTROWS (
            FILTER (
                ADDCOLUMNS (
                    SUMMARIZE (
                        dbo_jiraissue,
                        dbo_jiraissue[id],
                        "MAXID", CALCULATE (
                            MAX ( dbo_changestatus[ID] ),
                            dbo_changestatus[ChangDate] <= MaxDateView
                        )
                    ),
                    "STATUS", CALCULATE (
                        VALUES ( dbo_changestatus[NEWSTRING] ),
                        FILTER ( dbo_changestatus, dbo_changestatus[ID] = [MAXID] )
                    )
                ),
                [STATUS] = "In Progress"
            )
        ),
        ALL ( DimDate )
    )
Chart_In Progress = 
VAR MaxDateView =
    CALCULATE ( LASTDATE ( DimDate[Date] ) )
RETURN
    IF (
        MaxDateView = TODAY (),
        CALCULATE (
            DISTINCTCOUNT ( dbo_jiraissue[id] ),
            FILTER ( dbo_jiraissue, dbo_jiraissue[issuestatus] = "3" )
        ),
        CALCULATE (
            DISTINCTCOUNT ( dbo_changestatus[issueid] ),
            FILTER (
                ADDCOLUMNS (
                    SUMMARIZE (
                        FILTER ( ALL ( dbo_changestatus ), dbo_changestatus[ChangDate] <= MaxDateView ),
                        dbo_changestatus[issueid],
                        "MAXID", MAX ( dbo_changestatus[ID] )
                    ),
                    "STATUS", LOOKUPVALUE ( dbo_changestatus[NEWSTRING], dbo_changestatus[ID], [MAXID] )
                ),
                [STATUS] = "In Progress"
            ),
            ALL ( DimDate )
        )
    )

 

5 REPLIES 5
tanquoc0309
Helper II
Helper II

I try with LASTNONBLANK but the result is not correct 

I try with other formula and it worked.

Can anyone take a look my formula below and help to optimize it. thanks

Changed Status = 
VAR MAXDATEVIEW =
    CALCULATE ( LASTDATE ( DimDate[Date] ) )
VAR TEMPTABLE = 
            ADDCOLUMNS (
                SUMMARIZE (
                   Issue ,
                    Issue[id],
                    "MAXID", CALCULATE (
                        MAX ( IssueChangeCalculate[ID] ),
                        IssueChangeCalculate[ChangDate] <= MaxDateView
                    )
                ),
                "STATUS", 
                   CALCULATE (
                    VALUES ( IssueChangeCalculate[NEWSTRING] ),
                    FILTER ( IssueChangeCalculate, IssueChangeCalculate[ID] = [MAXID] ))
                )
            
RETURN
if(VALUES('Status'[Status]) = "Open",CALCULATE (
        DISTINCTCOUNT ( Issue[id] ),
        FILTER (
            TEMPTABLE,
            [STATUS] = "" ),FILTER(Issue,Issue[Created Date] <= MAXDATEVIEW
        ),  ALL ( DimDate )
    ),
 CALCULATE (
        DISTINCTCOUNT ( Issue[id] ),
        FILTER (
            TEMPTABLE,
            CONTAINS ( 'Status', 'Status'[Status_Satic], [STATUS] )
        ),  ALL ( DimDate )
    ))

HI @tanquoc0309,

 

Can you please share some some sample data or pbix for these to help us clarify for your scenario.

 

For optimize formula, you can also take a look at below link:

Optimizing duplicated DAX expressions using variables

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi @v-shex-msft

My scenario: I want to know the last status of each issue until the date and then count issue based on the status

I have 3 tables below:

1111.PNG

My expectation:

123123.PNG

Explaining: 

31-Jan:   2 issue created (Open) and no change this date -> 2 for Open and 0 for the rest status.

1-Feb:   Issue 1 (ID 1) changed to "In Progress" and Issue 2 (ID 2) still "Open" -> 1 for Open and 1 for In Progress

2-Feb: 

Issue 1: no change -> sill keep 1 for In Progress

Issue 2: Change to In Progress (ChangeID 5) then to Resolved (ChangeID 5) -> So Status of Issue 2 until 2-Fed: Resolved

=> 1 for In Progress and 1 for Resolved.

3-Feb, 4-Feb : No change -> still kip 1 In Progress and 1 Resolved

5-Feb:

Issue 1 goto Closed => 1 for Closed

Issue 2: no change -> 1 for resolved

==> 1 for closed & 1 for resolved.

 

Thanks & Best regards,

 

HI @tanquoc0309,

 

I also test with your sample data, but it seem like I can't add their nonexistent opened records.

I can only suggest to create crossjoin table with summary records.

 

16.PNG

 

Regards,

XIaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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.