CODEWITHSUNDEEP
powerbidax
sffc
sffc

Reputation: 61

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

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

Upvotes: 0

Views: 1436

Answers (2)

AntrikshSharma
AntrikshSharma

Reputation: 661

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
    )

Upvotes: 0

sffc
sffc

Reputation: 61

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

Upvotes: 0

Related Questions