topic Re: Create a measure - help! in DAX Commands and Tips

Create a measure - help!

pedrohenriquewe — Mon, 05 Jun 2023 21:07:20 GMT

I have two tables:

AllIssues:

key	Resolved
GCL-2379	11/04/2023
GCL-2397	27/03/2023
GSX-2137	27/03/2023

AllHistory:

key	Status	History New Value Start
GCL-2379	UP	15/03/2023
GCL-2379	BL	15/03/2023
GCL-2379	UP	16/03/2023
GCL-2379	END	11/04/2023
GCL-2397	UP	23/03/2023
GCL-2397	LT	24/03/2023
GCL-2397	END	27/03/2023
GSX-2137	UP	20/03/2023
GSX-2137	END	27/03/2023

I wanna count the number of keys that was "Status" = "UP" rightbefore the date of "Resolved" (table AllIssues). It seems to be easy but i am stuck =(

For the example above, the measure output would be 2, because only GSX-2137 and GCL-2379 was "UP" right before the date of 'Resolved'.

Re: Create a measure - help!

AlanFredes — Mon, 05 Jun 2023 22:00:36 GMT

If both tables have a relationship based on the "key" columns then a simple measure should be sufficient:
Count_UP=

CALCULATE(

COUNTROWS(AllHistory),

AllHistory[Status]=" UP",

AllHistory[History]<=MAX(AllIssues[Resolved])

)

When added to a table based on AllIssues columns the rowcontext should identify each Resolved date by row. See bellow Picture.

Re: Create a measure - help!

AlanFredes — Mon, 05 Jun 2023 22:06:53 GMT

Please mark it if it works.
Regards,

Re: Create a measure - help!

pedrohenriquewe — Tue, 06 Jun 2023 01:32:06 GMT

It doesn't work because I don't want all the "UP" statuses, I just want it to count when the UP status is immediately before the resolved date.

For example, for key GCL-2397, it will not increase my measure because the status right before 'resolved date' is 'LT' not 'UP'.

Re: Create a measure - help!

AlanFredes — Wed, 07 Jun 2023 15:31:31 GMT

Hi Pedro,

This should work. Assuming again there is an active relationship between both tables based on "Key" column.
I defined the right before 'resolve date' as trailing 10 days. You can modify it as you see fit.

Total UP =

VAR Sum_Table =

FILTER(

ADDCOLUMNS(AllHistory,

"To_Resolve",DATEDIFF(AllHistory[History],RELATED(AllIssues[Resolved]),DAY)

AllHistory[Status]=" UP" && [To_Resolve]>0 && [To_Resolve]<10

)

RETURN

COUNTROWS(Sum_Table)

Re: Create a measure - help!

pedrohenriquewe — Wed, 07 Jun 2023 17:55:51 GMT

Hello!

"Right Before" means the last Status before "Resolved" must be "UP". It does not matter if it was 10 days ago or 1000 days ago.

The measure have to count just two lines (Distinctcount by key).

GCL-2379

16/03/2023

GSX-2137

20/03/2023

Re: Create a measure - help!

AlanFredes — Wed, 07 Jun 2023 19:20:24 GMT

Ok,
Finally got the question.
So the below DAX function works. Same assumption as before regarding relationships between both tables.

Total UP2 =

VAR Sum_Table =

FILTER(

SUMMARIZE(

AllIssues,

AllIssues[key],

AllIssues[Resolved],

"Last Date",CALCULATE(MAX(AllHistory[History]),FILTER(AllHistory,AllHistory[History]<AllIssues[Resolved])),

"Last Status",LOOKUPVALUE(AllHistory[Status],AllHistory[History].[Date],CALCULATE(MAX(AllHistory[History]),FILTER(AllHistory,AllHistory[History]<AllIssues[Resolved])))

[Last Status]="UP"

)

RETURN

COUNTROWS(Sum_Table)

The summarize function creates the table you see beside the card (where the Total UP 2 is displayed). This summarized tables look for the last date before the 'resolved' date and then does a lookup for that status. Complete function then does a count row filtering un Status=UP.