Power BI Dax Error - The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value

0
votes

I'm effectively trying to accomplish what is demonstrated in this how to video, except to calculate production line down time data between stops/starts, but the logic gives the subject error. This error does not occur in the demonstration, so I don't understand where the issue is.

Can someone please help advise, or is there a better way to accomplish the above task relative to the data example provided below? There are ~27 unique events that will always have a stop/start before a new event can take place and I need to calculate the downtime between each stop/start.

https://www.youtube.com/watch?v=Q8iri3G1_x4

timeDurations = 
VAR indexNumber = MAX(Data[Index])
VAR previousIndexNumber = CALCULATE( MAX(Data[Index]), FILTER( ALLSELECTED(Data), Data[Index] < indexNumber))
VAR stopTime = VALUE( SELECTEDVALUE(Data[TimeStamp]))
VAR startTime = VALUE( CALCULATE( SELECTEDVALUE(Data[TimeStamp], FILTER( ALL(Data), Data[Index] = previousIndexNumber))))

RETURN
IF( indexNumber = CALCULATE( MIN(Data[Index], ALLSELECTED(Data))), 
    0,
        stopTime - startTime)

enter image description here

2
powerbidax
Your missing a ) after MIN(Data[Index] it should be MIN(Data[Index]) – Jon
It appears to still throw the same error. – sffc
if you provide a dummy sample data i'll get you that formula to work for you. – Roger Steinberg

2 Answers

0
votes

I gave up on trying to accomplish this in Power BI and decided to convert in Excel then refresh the dataset in Power BI instead. This would be my suggestion to anyone looking for a similar solution....

=IF(C2=C3, A2-A3,0) then custom format the value into hh:mm:ss..

0
votes

Try this:

timeDurations =
VAR indexNumber =
    MAX ( Data[Index] )
VAR previousIndexNumber =
    CALCULATE (
        MAX ( Data[Index] ),
        FILTER ( ALLSELECTED ( Data ), Data[Index] < indexNumber )
    )
VAR stopTime =
    VALUE ( SELECTEDVALUE ( Data[TimeStamp] ) )
VAR startTime =
    VALUE (
        CALCULATE (
            MAX ( Data[TimeStamp] ),
            FILTER ( ALL ( Data ), Data[Index] = previousIndexNumber )
        )
    )
RETURN
    IF (
        indexNumber = CALCULATE ( MIN ( Data[Index] ), ALLSELECTED ( Data ) ),
        0,
        stopTime - startTime
    )