Summarize error with The express refers to multiple columns.

[Anonymous]

Hi,

I am trying to build a table of EMP headcounts based of their Hours working group,. The dataset is like;

Job                                      EMP Names                                        Total

Civil                                     James                                                  200

Civil                                    Paul                                                      2000

Pipe                                    Paul                                                      4000

Pipe                                    Troy                                                     700

Market                               Tom                                                      500

I have;

• Table: Append1
• Column 1: Append1[JOB] 
• Column 2: Append1[Employee Name]
• Column 3: Append1[Total]  - Actual work hours

And below is the folmula that I am using;

Experience Table = summarize(Append1,Append1[JOB],Append1[Employee Name],"0 to 250",Calculate(distinctcount(Append1[Employee Name]),Filter(Append1,Append1[Total]<=250)),"500 to 1000",Calculate(distinctcount(Append1[Employee Name]),Filter(Append1,And(Append1[Total]>250,Append1[Total]<=500))),"1000 to 2000",Calculate(distinctcount(Append1[Employee Name]),Filter(Append1, And(Append1[Total]>1000,Append1[Total]<=2000))),"2000 to 4000",Calculate(distinctcount(Append1[Employee Name]),Filter(Append1, And(Append1[Total]>2000,Append1[Total]<=4000))),"Over 4000",Calculate(distinctcount(Append1[Employee Name]),Filter(Append1,Append1[Total]>4000)))

And my end result that I want is;

JOB                 >250     250 to 500     500 to 1000      1000 to 2000      2000 to 4000       >4000

Civil                    1               0                    0                        1                          0                      0

Pipe                    0               0                    1                        0                          1                      0

Market                0              1                      0                      0                          0                      0

I am trying to find out why it comes up with error; "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

Pleas help.

Thank you,

[amitchandak · Accepted solution]

@Anonymous 

If you are not doing any aggregation on Append1[Total].

Create a new column in the append table and use it?

Bucket =

Switch(true(),
Append1[Total]<=250,"0 to 250",
Append1[Total]<=500,"250 to 550",
Append1[Total]<=1000,"500 to 1000",
Append1[Total]<=2000,"1000 to 2000",
Append1[Total]<=2000,"2000 to 4000",
"Over 4000"
)

[v-chuncz-msft · Accepted solution]

@Anonymous 

You need to Create a calculated table not column or measure.

[amitchandak · Accepted solution]

@Anonymous 

If you are not doing any aggregation on Append1[Total].

Create a new column in the append table and use it?

Bucket =

Switch(true(),
Append1[Total]<=250,"0 to 250",
Append1[Total]<=500,"250 to 550",
Append1[Total]<=1000,"500 to 1000",
Append1[Total]<=2000,"1000 to 2000",
Append1[Total]<=2000,"2000 to 4000",
"Over 4000"
)