Power B i Desktop Single value from Multiple Error

[Magrfa]

I am new to power bi but am getting the error message

A single value for column 'Days Code' in table 'UserSessionGrouped' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

I have a dataset from a software package showing a table of users and a seperate table of different login sessions.

I wish to create a value for how many months it is since each person logged in.

I have grouped the sessions table to get one record for each person

I have produced the value applicable to each person in the sessions grouped table and now want that in the user table

I have tried to use the formula below to bring across that value in certain circumstances but it is producing an error

Full Code = if([Active Code]>4,[Active Code],UserSessionGrouped[Days Code])

Any help to get this working would be gratefully apreciated

[Anonymous · Accepted solution]

Hi @Magrfa ,

Please try this measure:

Full Code = 
IF (
    MAX ( [Active Code] ) > 4,
    MAX ( [Active Code] ),
    CONCATENATEX (
        FILTER (
            ALL ( 'UserSessionGrouped' ),
            'UserSessionGrouped'[Active Code] = MAX ( 'UserSessionGrouped'[Active Code] )
        ),
        'UserSessionGrouped'[Days Code],
        ","
    )
)

Or column:

Full Code = 
VAR _value =
    CONCATENATEX (
        FILTER (
            ALL ( 'UserSessionGrouped' ),
            'UserSessionGrouped'[Active Code]
                = EARLIER ( 'UserSessionGrouped'[Active Code] )
        ),
        'UserSessionGrouped'[Days Code],
        ","
    )
VAR _result =
    IF (
        'UserSessionGrouped'[Active Code] > 4,
        CONVERT('UserSessionGrouped'[Active Code],STRING),
        _value
    )
RETURN
    _result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[amitchandak]

@Magrfa , if [Active Code] is column, below should work

Full Code = if([Active Code]>4,[Active Code],UserSessionGrouped[Days Code])

if [Active Code] is measure , a new measure

Full Code = if([Active Code]>4,[Active Code],max(UserSessionGrouped[Days Code]))

[Magrfa]

Thankyou for the quick reply.

It is a colomn, however the measure code you provide does give a value where the original does not.

This however gives me the largest value (4) in that table for every record, when most are 1 a few 2 and only 2 are a 4. See screenshots below