topic Re: Finding the different in a value when a condition from another column is met in DAX Commands and Tips

Finding the different in a value when a condition from another column is met

xsaldana — Mon, 09 May 2022 02:16:13 GMT

Hello! I am having trouble finding the difference in unix time between readings at the same location. Below is an example of a table. The "Time in between readings" is the column I am attempting to create. I highlighted the table in terms of location to emphasize that the difference only needs to be taken if at the same location.

This is the command I have, but it does not work properly. Any suggestions on how to do this?

Time in between readings = IF(ISBLANK(Data[temperature]) = FALSE() && Data[Location = Data[Location], VAR Diff = Data[unix time] - CALCULATE(SUM(Data[unix time]), Filter(Data, Data[Index] = EARLIER(Data[Index]) ) )

return

IF(Diff = VALUE(Data[unix time]), 0, diff))

Re: Finding the different in a value when a condition from another column is met

amitchandak — Mon, 09 May 2022 02:55:15 GMT

xsaldana , New column

new column =
var _max = max(filter(Table, Data[Location] = earlier(Data[Location]) && Data[unix time] < earlier( Data[Location] )),[unix time])
return
IF(not(ISBLANK(Data[temperature])) , Data[temperature] - max(filter(Table, Data[Location] = earlier(Data[Location]) && Data[unix time] =_max ), Data[temperature]) , blank())

Re: Finding the different in a value when a condition from another column is met

xsaldana — Mon, 09 May 2022 05:07:46 GMT

amitchandak

Hello, thank you for taking the time to look at this! Unfortunately, I get an error when I use your command. It says "the expression refers to multiple columns. multiple columns cannot be converted to scalar value.

Re: Finding the different in a value when a condition from another column is met

tamerj1 — Mon, 09 May 2022 03:18:17 GMT

Hi xsaldana

you may try

Time in between readings = VAR CurrentTime = Data[unix time] VAR LocationTable = CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[Location] ) ) VAR PreviousTime = MAXX ( FILTER ( CurrentTime, Data[unix time] < CurrentTime && Data[temperature] <> BLANK () ), Data[unix time] ) RETURN IF ( NOT ( ISBLANK ( Data[temperature] ) ), CurrentTime - PreviousTime )

Re: Finding the different in a value when a condition from another column is met

xsaldana — Mon, 09 May 2022 05:07:16 GMT

tamerj1

Thank you for suggesting this! Unfortunately, it did not work. It's stuck on "working on it"

Re: Finding the different in a value when a condition from another column is met

tamerj1 — Mon, 09 May 2022 08:55:42 GMT

xsaldana
Apparantly this need to be optimized. However, and before going into further complicatios would you please try this

Time in between readings = VAR CurrentTime = Data[unix time] VAR LocationTable = CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[Location] ) ) VAR T1 = FILTER ( CurrentTime, Data[unix time] < CurrentTime ) VAR T2 = FILTER ( T1, Data[temperature] <> BLANK () ) VAR PreviousTime = MAXX ( T2, Data[unix time] ) RETURN IF ( NOT ( ISBLANK ( Data[temperature] ) ), CurrentTime - PreviousTime )

Re: Finding the different in a value when a condition from another column is met

xsaldana — Mon, 09 May 2022 12:23:22 GMT

tamerj1

Thank you! It seem this one runs but it doesn't work. The error I get is "The FILTER function expects a table expression for argument", but a string or numeric expression was used.

PowerBI highlights red "CurrentTime" in

VAR T1 =
    FILTER ( CurrentTime,

Re: Finding the different in a value when a condition from another column is met

tamerj1 — Mon, 09 May 2022 14:14:48 GMT

xsaldana

My mistake, I copied the wrong variable.

Time in between readings = VAR CurrentTime = Data[unix time] VAR LocationTable = CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[Location] ) ) VAR T1 = FILTER ( LocationTable, Data[unix time] < CurrentTime ) VAR T2 = FILTER ( T1, Data[temperature] <> BLANK () ) VAR PreviousTime = MAXX ( T2, Data[unix time] ) RETURN IF ( NOT ( ISBLANK ( Data[temperature] ) ), CurrentTime - PreviousTime )

Re: Finding the different in a value when a condition from another column is met

tamerj1 — Mon, 09 May 2022 13:02:43 GMT

xsaldana
Another solution with some help from power query (if applicaple) https://www.dropbox.com/t/Gj8t4ZxelU0zRmqA

Time in between readings = VAR CurrentIndex = Data[Index] VAR CurrentTime = Data[unix time] VAR PreviousTime = CALCULATE ( VALUES ( Data[unix time] ), ALLEXCEPT ( Data, Data[Location] ), Data[Index] = CurrentIndex - 1 ) RETURN IF ( NOT ISBLANK ( Data[temperature] ) && NOT ISBLANK ( PreviousTime ), CurrentTime - PreviousTime )

Re: Finding the different in a value when a condition from another column is met

xsaldana — Mon, 09 May 2022 23:30:43 GMT

That worked. Thank you!!