Forum Discussion

ashleybaldwin's avatar
ashleybaldwin
Frequent Visitor
6 years ago
Solved

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 mult...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    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

v-lili6-msft's avatar
v-lili6-msft
Community Support
6 years ago

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's avatar
ashleybaldwin
Frequent Visitor
6 years ago

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