Multiple columns cannot be converted to a scalar value|tried both measure and calculated column

[Anonymous]

I am trying to retrieve specific KPI status % on donut(Pass,fail,pending) using month slicer on visual but getting the error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

I tried it with both measure and calculated column. Although i doubt on using calculated column becoz im not doing any calculations in table columns , it is retrieval of text status . 

any help will be appriciated 

KPI Status Name =

VAR ThisMonthKPIStatusName =

SWITCH (

SELECTEDVALUE ( 'Date Table'[MonthName]),

"July", FILTER('07142020','07142020'[KPI Status]),

"August", FILTER('08042020','08042020'[KPI Status])

)

RETURN

ThisMonthKPIStatusName

[Icey]

Hi @Anonymous ,

Please refer to this blog to provide sample data for test: How to provide sample data in the Power BI Forum.

Best Regards,

Icey

[amitchandak]

@Anonymous , When you created a column or measure you expected to return a column of measure

This is returning Table. Filter return Table

FILTER('07142020','07142020'[KPI Status])

[Anonymous]

Hi @amitchandak 

thanks for quick response, FILTER('07142020','07142020'[KPI Status]) , here KPI [status] is column of table 07142020

can you suggest what should i ammend to get expected outcome. 

[amitchandak]

@Anonymous , then it should be like

KPI Status Name =
VAR ThisMonthKPIStatusName =
SWITCH (
SELECTEDVALUE ( 'Date Table'[MonthName]),
"July", '07142020'[KPI Status],
"August", '08042020'[KPI Status]
)

Not this can not be column because you use selected value so return measure

KPI Status Name =
VAR ThisMonthKPIStatusName =
SWITCH (
SELECTEDVALUE ( 'Date Table'[MonthName]),
"July", Max('07142020'[KPI Status]),
"August", max('08042020'[KPI Status])
)

return ThisMonthKPIStatusName 

[Anonymous]

Hi @amitchandak  

but it return max value, i am looking for all values (Pass,fail,pending) to return so that indiviual status % can appear on donut chart

[amitchandak]

@Anonymous , that will happen because of row context.

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

Hi @amitchandak  i did not get attachment link here to attach pbix file

I must be doing something wrong here in getting output value, what i read is we should use calculated column and not measure if we are looking for multi value return.

i have two tables 07142020 and 08142020 and i created a calculated column in table 07142020. Although this calculated column is also giving me same error but i have one question here if you can guide me.

how can i create a indepedent calculated column (even there is not math calculation in it , as this is just filter values) because if i create it in one of the table then i need to do relationship to map values and multi column relationship is not supported in Power BI as of now (there is already 1 relationship exists)

or neither calculation column nor measure is rite solution for this to return multiple values dependent upon selected slicer month value