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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
pedrohenriquewe
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:

keyStatus  History New Value Start
GCL-2379 UP15/03/2023
GCL-2379 BL15/03/2023
GCL-2379 UP16/03/2023
GCL-2379 END11/04/2023
GCL-2397 UP23/03/2023
GCL-2397 LT24/03/2023
GCL-2397 END27/03/2023
GSX-2137 UP20/03/2023
GSX-2137 END27/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

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.

AlanFredes_0-1686163079632.png

 



View solution in original post

6 REPLIES 6
AlanFredes
Resolver III
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.

AlanFredes_0-1686002353353.png

 

Please mark it if it works.
Regards,

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

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)

 

AlanFredes_0-1686151858456.png

 

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 UP16/03/2023
GSX-2137 UP20/03/2023

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.

AlanFredes_0-1686163079632.png

 



Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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