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

[ashleybaldwin]

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

Number	Created	ServiceDesk	3rd Party	Internal	Problem Management	Escalation
1	01/01/2019	True				Normal
2	02/01/2019		True			Overdue

[v-lili6-msft · Accepted solution]

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

[ashleybaldwin]

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

[amitchandak]

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

[ashleybaldwin]

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