if and Switch | Multiple columns cannot be converted to a scalar value

[Anonymous]

Hi,

im trying to show table as per selection in month slicer. Depending upon month selected ,it will show table generated from separate tables.

i tried both switch and if but getting error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

option 1 i tried - 

test =

if(FILTER('Date Table','Date Table'[MonthName])="July",FILTER('07142020','07142020'[KPI Name]),FILTER('08042020','08042020'[KPI Name]))

option 2 i tried  - 

KPI Name Switch =

VAR ThisMonthKPI =

SWITCH (

SELECTEDVALUE ( 'Date Table'[MonthName]),

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

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

)

VAR LastMonthKPI =

SWITCH (

SELECTEDVALUE ( 'Date Table'[MonthName]),

"July", 0,

"August", FILTER('07142020','07142020'[KPI Name]),

"September", FILTER('08042020','08042020'[KPI Name])

)

RETURN

IF (

ISFILTERED ( 'Date Table'[MonthName]),

IF ( ISBLANK ( LastMonthKPI ), 0, ThisMonthKPI),

0

)

[Pragati11]

Hi @Anonymous ,

Are you creating these as MEASURES?

If yes, try creating them as calculated COLUMNS. The error will not come.

Thanks,

Pragati

[Anonymous]

Hi @Pragati11 

i tried both ways, Option 1 which i mentioned is not measure but table, Option 2 is measure

also, i hvnt use any measure in calculatios, its all calculated columns and table columns but still getting the same error

[Pragati11]

Hi @Anonymous ,

Not sure why you are creating a table as OPTION 1.

For OPTION 2 try creating a calculated column, rather than a measure. You can create columns in Power BI similar to how you create measures.

Thanks,

Pragati

[Anonymous]

Hi @Pragati11 

i tried it as calculated column just pasted the same formula by choosing the new column instead of measure but it is giving same error.

can u check the formula once in intial post and correct me if u see any error

what im trying to do is -

- on main page ,i have month slicer and respective card showing count of that month which is workinng fine - Page1

- i created the bookmark link on same page, on click of which user will navigate to another details page - Page2

- now page 2 contains table visual with more details and same synch slicer

- now here my intial post farmula comes where im trying the table to show data as per month selection(july,Aug) 

[Pragati11]

Hi @Anonymous ,

Just out of curiosity - PAGE 2 is a detailed tab of PAGE 1 having detailed information at a MONTH level. Right?

Why don't you use drill-through functionality to achieve this rather than creating this complicated calculation? See article below:

https://radacad.com/power-bi-drill-through

Thanks,

Pragati

[Anonymous]

Hi @Pragati11 

i did drill through option as well and is working fine at powe bi service ,although i did not find it at power bi desktop level.

but anyways, my problem is not navigating between pages, my problem is showing table data as per month selection on page 2 which is not working so im trying to fetch data as per month is july or august if you see my dax expression...it will be of great help if anything u see wrong there

[Pragati11]

HI @Anonymous ,

Drill-through is not for navigating between pages. It is for drilling into the information at a certain level - in your case it is MONTH level.

I am not sure why you are not able to apply drill-through capability in Power BI Desktop. Check this article on implementing drill-through in Power BI Desktop.

https://exceltown.com/en/tutorials/power-bi/powerbi-com-and-power-bi-desktop/power-bi-charts-and-visualization/drillthrough-filtering-simply/

Also, it's very hard to spot the mistake in a DAX expression if you can't test it.

Thanks,

Pragati