Forum Discussion
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:
| 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 |
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
4 Replies
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,
LinLin,
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') ) )
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-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601Unfortunately 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') )
