cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

Hi,

 

I am trying to calculate the percentage of rows with the value "True", each row however needs to run against a different column.

I am however getting the error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.", any help/advise would be hugely apprecaited.

 

Please see my Calculated column("% SLA met") and table structure below with sample data:

 

% SLA met =
VAR AssignmentVar = 'Last 12 Months'&"["&'Assignment SLA'[Assignments]&"]"
RETURN 1 - DIVIDE(
CALCULATE(
COUNTA(
'Last 12 Months'[Number]),
'Last 12 Months'[escalation] IN {("Overdue")},
AssignmentVar IN {("True")}),
COUNTROWS('Last 12 Months')
)
 
 
Assignments% SLA met
ServiceDesk 
3rd Party 
Internal 
Problem Management 

 

NumberCreatedServiceDesk3rd PartyInternalProblem ManagementEscalation
101/01/2019True   Normal
202/01/2019 True  Overdue
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar valu

hi @ashleybaldwin 

The problem is that in your formula 'Last 12 Months' will return a table and i think you just want to return text "Last 12 Months",
Please use "" instead of ''
% SLA met =
VAR AssignmentVar = 'Last 12 Months' & "["&'Assignment SLA'[Assignments]&"]"
RETURN 1 - DIVIDE(
CALCULATE(
COUNTA(
'Last 12 Months'[Number]),
'Last 12 Months'[escalation] IN {("Overdue")},
AssignmentVar IN {("True")}),
COUNTROWS('Last 12 Months')
)
and also for this conditional AssignmentVar IN {("True")}), It has the wrong logic
For your case, you want rows value in 'Assignment SLA' table calculate corresponding columns in 'Last 12 Months' table, you could only use these two ways:
1. Unpivot the table 'Last 12 Months' and then create a relationship between these two tables
https://radacad.com/pivot-and-unpivot-with-power-bi
Then create a the calculate column by DISTINCTCOUNT Function.
2. you need to create a IF/SWITCH column as below:
Note: please keep [ServiceDesk]/[3rd Party]/[Internal]/[Problem Management] is

% SLA met =
SWITCH (
    'Assignment SLA'[Assignments],
    "ServiceDesk", 1
        - DIVIDE (
            CALCULATE (
                COUNTA ( 'Last 12 Months'[Number] ),
                'Last 12 Months'[ServiceDesk] = TRUE ()
            ),
            COUNTROWS ( 'Last 12 Months' )
        ),
    "3rd Party", 1
        - DIVIDE (
            CALCULATE (
                COUNTA ( 'Last 12 Months'[Number] ),
                'Last 12 Months'[3rd Party] = TRUE ()
            ),
            COUNTROWS ( 'Last 12 Months' )
        ),
    "Internal", 1
        - DIVIDE (
            CALCULATE (
                COUNTA ( 'Last 12 Months'[Number] ),
                'Last 12 Months'[Internal] = TRUE ()
            ),
            COUNTROWS ( 'Last 12 Months' )
        ),
    "Problem Management", 1
        - DIVIDE (
            CALCULATE (
                COUNTA ( 'Last 12 Months'[Number] ),
                'Last 12 Months'[Problem Management] = TRUE ()
            ),
            COUNTROWS ( 'Last 12 Months' )
        )
)


and here is sample pbix file ,please try it.
 
Regards,
Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Highlighted
Super User IV
Super User IV

Re: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar valu

In, calculate ,on the same column, you can put multiple filters other cases you need to use filter clause

 

Like

% SLA met =
VAR AssignmentVar = 'Last 12 Months'&"["&'Assignment SLA'[Assignments]&"]"
RETURN 1 - DIVIDE(
CALCULATE(
COUNTA(
'Last 12 Months'[Number]),
filter('Last 12 Months','Last 12 Months'[escalation] IN {("Overdue")} && 
AssignmentVar IN {("True")})),
COUNTROWS('Last 12 Months')
)

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Frequent Visitor

Re: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar valu

Unfortunately this doesn't work either, I have also tried dropping the escalation filter for testing and get the same error.

I think this is specifically due to each row checking a different column, any other ideas?

 

% SLA met = 
VAR AssignmentVar = 'Last 12 Months'&"["&'Assignment SLA'[Assignments]&"]"
RETURN 1 - DIVIDE(
CALCULATE(
COUNTA(
'Last 12 Months'[Number]),
AssignmentVar IN {("True")}),
COUNTROWS('Last 12 Months')
)
Highlighted
Community Support
Community Support

Re: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar valu

hi @ashleybaldwin 

The problem is that in your formula 'Last 12 Months' will return a table and i think you just want to return text "Last 12 Months",
Please use "" instead of ''
% SLA met =
VAR AssignmentVar = 'Last 12 Months' & "["&'Assignment SLA'[Assignments]&"]"
RETURN 1 - DIVIDE(
CALCULATE(
COUNTA(
'Last 12 Months'[Number]),
'Last 12 Months'[escalation] IN {("Overdue")},
AssignmentVar IN {("True")}),
COUNTROWS('Last 12 Months')
)
and also for this conditional AssignmentVar IN {("True")}), It has the wrong logic
For your case, you want rows value in 'Assignment SLA' table calculate corresponding columns in 'Last 12 Months' table, you could only use these two ways:
1. Unpivot the table 'Last 12 Months' and then create a relationship between these two tables
https://radacad.com/pivot-and-unpivot-with-power-bi
Then create a the calculate column by DISTINCTCOUNT Function.
2. you need to create a IF/SWITCH column as below:
Note: please keep [ServiceDesk]/[3rd Party]/[Internal]/[Problem Management] is

% SLA met =
SWITCH (
    'Assignment SLA'[Assignments],
    "ServiceDesk", 1
        - DIVIDE (
            CALCULATE (
                COUNTA ( 'Last 12 Months'[Number] ),
                'Last 12 Months'[ServiceDesk] = TRUE ()
            ),
            COUNTROWS ( 'Last 12 Months' )
        ),
    "3rd Party", 1
        - DIVIDE (
            CALCULATE (
                COUNTA ( 'Last 12 Months'[Number] ),
                'Last 12 Months'[3rd Party] = TRUE ()
            ),
            COUNTROWS ( 'Last 12 Months' )
        ),
    "Internal", 1
        - DIVIDE (
            CALCULATE (
                COUNTA ( 'Last 12 Months'[Number] ),
                'Last 12 Months'[Internal] = TRUE ()
            ),
            COUNTROWS ( 'Last 12 Months' )
        ),
    "Problem Management", 1
        - DIVIDE (
            CALCULATE (
                COUNTA ( 'Last 12 Months'[Number] ),
                'Last 12 Months'[Problem Management] = TRUE ()
            ),
            COUNTROWS ( 'Last 12 Months' )
        )
)


and here is sample pbix file ,please try it.
 
Regards,
Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Frequent Visitor

Re: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar valu

Lin,

You have saved me from the slow meltdown I was moving towards.

Using your suggestion I realised that my original logic was actually off as well, eneded up using the below and works perfectly.

Thank you! 🙂

 

% SLA met = 
SWITCH (
    'Assignment SLA'[Assignments],
    "ServiceDesk", 1
        - DIVIDE (
            CALCULATE (
                COUNTA ( 'Last 12 Months'[Number] ),
                'Last 12 Months'[ServiceDesk] = "True",
                'Last 12 Months'[escalation] = "Overdue"
            ),
            CALCULATE (
                COUNTA ( 'Last 12 Months'[Number] ),
                'Last 12 Months'[ServiceDesk] = "True"
            )
        ),
    "3rd Party", 1
        - DIVIDE (
            CALCULATE (
                COUNTA ( 'Last 12 Months'[Number] ),
                'Last 12 Months'[3rd Party] = "True",
                'Last 12 Months'[Escalation] = "Overdue"
            ),
            CALCULATE (
                COUNTA ( 'Last 12 Months'[Number] ),
                'Last 12 Months'[3rd Party] = "True"
            )
        ),
    "Internal", 1
        - DIVIDE (
            CALCULATE (
                COUNTA ( 'Last 12 Months'[Number] ),
                'Last 12 Months'[Internal] = "True",
                'Last 12 Months'[Escalation] = "Overdue"
            ),
            CALCULATE (
                COUNTA ( 'Last 12 Months'[Number] ),
                'Last 12 Months'[Internal] = "True"
            )
        ),
    "Problem Management", 1
        - DIVIDE (
            CALCULATE (
                COUNTA ( 'Last 12 Months'[Number] ),
                'Last 12 Months'[Problem Management] = "True",
                'Last 12 Months'[Escalation] = "Overdue"
            ),
            CALCULATE (
                COUNTA ( 'Last 12 Months'[Number] ),
                'Last 12 Months'[Problem Management] = "True"
            )
        ),
    "Total", 1
        - DIVIDE (
            CALCULATE (
                COUNTA ( 'Last 12 Months'[Number] ),
                'Last 12 Months'[Escalation] = "Overdue"
            ),
            COUNTROWS('Last 12 Months')
            )
)

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Kudoed Authors