cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## Create a measure - help!

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'.

1 ACCEPTED SOLUTION
Resolver III

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.

6 REPLIES 6
Resolver III

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.

Resolver III

Please mark it if it works.
Regards,

Regular Visitor

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'.

Resolver III

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(
"To_Resolve",DATEDIFF(AllHistory[History],RELATED(AllIssues[Resolved]),DAY)
),
AllHistory[Status]=" UP" && [To_Resolve]>0 && [To_Resolve]<10
)
RETURN
COUNTROWS(Sum_Table)

Regular Visitor

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 UP 16/03/2023
 GSX-2137 UP 20/03/2023
Resolver III

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors