Getting table of values despite getting 1 row and selecting 1 column

[Anonymous]

Hi everybody,

I'm currently having some problems debugging an error I'm getting: "A table of multiple values was supplied where a single value was expected.". This is a bit confusing, because the value I'm trying to get is a single column of a single row. FYI: the table I've created this calculated column in is the same table I'm trying to get information from. My DAX:

LoadId = IF(TableX[OrionFunctionName] <> "LoadClaim",
            SELECTCOLUMNS(
                TOPN(1,
                    FILTER(TableX, TableX[ClaimId] = EARLIER(TableX[ClaimId])),
                    TableX[CreatedLocalDateTime],
                    DESC),
            "LoadId",
            TableX[Id]),TableX[Id])

As far as I know it's not possible to get anything other than 1 value from 1 column from 1 row, so getting an error saying there's a table of multiple values is a bit weird. I can't find a way to actually see what that table of values is as well, so I'm just grasping at straws right now.

[v-kelly-msft · Accepted solution]

Hi @Anonymous ，

When using "Topn"funtion, if it is filtering multiple rows with the same value in a column,it will return error,such as below:

As topN returns a single value,so if the data is modified as below,then your expression will work:

So here,best suggestion for you is to modify your dax expression to the below one:

LoadId = IF('Table'[OrionFunctionName] <> "LoadClaim",
           VAR t =
        CALCULATE (
            MAX ( 'Table'[CreatedLocalDateTime] ),
            FILTER ( 'Table', 'Table'[ClaimId] = EARLIER ('Table'[ClaimId]) )
        )
     Return
     CALCULATE(MAX('Table'[ID]),FILTER('Table','Table'[ClaimId]=EARLIER('Table'[ClaimId]) && 'Table'[CreatedLocalDateTime]= t),
            // SELECTCOLUMNS(
                // TOPN(1,
                //     FILTER('Table', 'Table'[ClaimId] = EARLIER('Table'[ClaimId])),
                //     'Table'[CreatedLocalDateTime],
                //     DESC),
            'Table'[ID]))

For the related .pbix file,pls click here.

Best Regards,
Kelly

[Anonymous]

Ahh, that explains a lot. I kept thinking the TopN was absolute and didn't think about that. Thanks!