A single value for column cannot be determined. This can happen when a measure formula refers...

[Anonymous]

Hi, I am having this issue a lot, and I change my code and it always come back, can someone help me? Why is appearing? I don't want to agregate in min, max, count or sum. I am just trying to refer to the column and bring its values. I have the code in power bi as a calcultaed column and I want it in DAX Studio.

Error: A single value for column d_PBU in table Validated_PD 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.

My column PBU contains different values, and I only want to use CS or EDS (ignore the rest of the values ej. AA, ACD, HBT), if it CS or EDS then in the column ultimate parent if it says "general motors" I want to have "GM" and if not "Outside". If is not CS or EDS (if is AA, ACD, HBT) then blank.

In Power BI DAX

GM_Outside =

IF(OR(VALIDATED_PD[d_PBU] = "CS", VALIDATED_PD[d_PBU] = "EDS"),

IF(VALIDATED_PD[a_UltimateParent] = "GENERAL MOTORS","GM","Outside"),

BLANK())

DAX STUDIO:

EVALUATE
    ADDCOLUMNS (
        SUMMARIZE (
            VALIDATED_PD,
            VALIDATED_PD[d_Customer_Name],
            VALIDATED_PD[m_Group_Currency_Amount]),
        "Current", [a_Current],
        "Total Overdue", [a_TotalOverdue],
        "1-30", [a_1-30],
        "31-60", [a_31-60],
        "61-90", [a_61-90],
        "Over 90", [a_Over90],
        "GM_Outside",
            VAR PBU = VALIDATED_PD[d_PBU]
            VAR UltimateParent = VALIDATED_PD[a_UltimateParent]
            RETURN
                IF ( 
                    OR (PBU = "CS", PBU = "EDS"),
                    IF (UltimateParent = "General Motors", "GM", "Outside"),
                    Blank ()))

[tamerj1]

Hi @Anonymous 

please try

GM_Outside =
CONCATENATEX (
CALCULATETABLE ( VALIDATED_PD ),
IF (
OR ( VALIDATED_PD[d_PBU] = "CS", VALIDATED_PD[d_PBU] = "EDS" ),
IF ( VALIDATED_PD[a_UltimateParent] = "GENERAL MOTORS", "GM", "Outside" ),
BLANK ()
),
UNICHAR ( 10 )
)

[Anonymous]

Not working

[tamerj1]

@Anonymous 

You need to specify the column alias name before the code

[Anonymous]

Still the same issue

[tamerj1]

@Anonymous 

Put it inside quotations please. 

[Anonymous]

I also have tried that one

[tamerj1]

 

 

@Anonymous 

[Anonymous]

It gives me everything in one row instead a value for each row

[tamerj1]

@Anonymous 
Please try

"GM_Outside",
CONCATENATEX (
    SUMMARIZE (
        CALCULATETABLE ( VALIDATED_PD ),
        VALIDATED_PD[d_PBU],
        VALIDATED_PD[a_UltimateParent]
    ),
    IF (
        VALIDATED_PD[d_PBU] IN { "CS", "EDS" },
        IF ( VALIDATED_PD[a_UltimateParent] = "GENERAL MOTORS", "GM", "Outside" )
    ),
    UNICHAR ( 10 )
)