topic Re: A single value for column cannot be determined. This can happen when a measure formula refers... in DAX Commands and Tips

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

Anonymous — Mon, 24 Apr 2023 16:27:02 GMT

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 ()))

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

tamerj1 — Mon, 24 Apr 2023 16:48:40 GMT

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

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

Anonymous — Mon, 24 Apr 2023 17:05:32 GMT

Not working

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

tamerj1 — Mon, 24 Apr 2023 17:12:38 GMT

Anonymous

You need to specify the column alias name before the code

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

Anonymous — Mon, 24 Apr 2023 17:21:22 GMT

Still the same issue

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

tamerj1 — Mon, 24 Apr 2023 18:12:21 GMT

Anonymous

Put it inside quotations please.

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

Anonymous — Mon, 24 Apr 2023 19:37:38 GMT

I also have tried that one

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

tamerj1 — Mon, 24 Apr 2023 19:48:02 GMT

Anonymous

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

Anonymous — Mon, 24 Apr 2023 21:29:46 GMT

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

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

tamerj1 — Tue, 25 Apr 2023 10:07:00 GMT

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