Forum Discussion

ChrisCollao's avatar
ChrisCollao
Frequent Visitor
3 years ago

Events Correlation

Hi all,   I'm trying to obtain a graph but I've couldn't find how to resolve it.   I have data from electronic failures of machinery. Basically, it's a table like this: Asset Datetime E...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Community Champion
3 years ago

ChrisCollao This doesn't give the exact results in your matrix but you might be able to tweak the logic to get it the way you want. I'm probably missing something obvious. PBIX is attached below signature.

Measure = 
    VAR __Window = 1/24
    VAR __EventRow = MAX('Events'[Event])
    VAR __EventColumn = MAX('Table'[Event])
    VAR __Table = SELECTCOLUMNS(DISTINCT('Table'[Asset]), "__Asset", [Asset])
    VAR __Table1 = ADDCOLUMNS(__Table, "__RefDateTime", MAXX(FILTER('Table', [Asset] = [__Asset] && [Event] = __EventRow),[Datetime]))
    VAR __Table2 = ADDCOLUMNS(__Table1, "__EndDateTime", [__RefDateTime] + __Window)
    VAR __Table3 = ADDCOLUMNS(__Table2, "__Occurences", COUNTROWS(FILTER('Table', [Event] = __EventColumn && [Datetime] >= [__RefDateTime] && [Datetime] <= [__EndDateTime])))
    VAR __Count = COUNTROWS(__Table3)
    VAR __Occurences = SUMX(__Table3, [__Occurences])
    VAR __Result = DIVIDE(__Occurences, __Count) + 0
RETURN
    __Result

 

ChrisCollao's avatar
ChrisCollao
Frequent Visitor
3 years ago

Hi Greg_Deckler , thanks for your answer,

 

I've being trying with your proposition but I couldn't obtain the right values 😞 I don't know if it's not properly counting through time window or assets. Will keep trying anyway.

 

Thanks.