topic Re: Dax code for multiple columns cannot be converted to a scalar value in DAX Commands and Tips

Dax code for multiple columns cannot be converted to a scalar value

Shruthi96 — Tue, 05 Apr 2022 14:51:06 GMT

Hi Team,

I want help in fixing the DAX code, I have table called "KPI-color" and below is the measure which I have used. Let me know if any one from this group can able to fix this. Thanks

Coummincation =

VAR countrow = if(CALCULATE(COUNTROWS('KPI - color'),'KPI - color'[Tag] >0),0,1)

VAR TOPROWS1 =

GENERATE (

VALUES ( 'KPI - color'[Service] ),

VAR mytable =

CALCULATETABLE ( TOPN ( 1, 'KPI - color' , [Tag], DESC) )

RETURN

SUMMARIZE ( mytable, 'KPI - color'[Tag] )

)

VAR TOPROWS2 =

GENERATE (

VALUES ( 'KPI - color'[Service] ),

VAR mytable =

CALCULATETABLE ('KPI - color')

RETURN

SUMMARIZE ( mytable, 'KPI - color'[Tag] )

)

VAR TOPROWS = if(countrow,toprows1,TOPROWS2)

VAR filteredRowsTable =

CALCULATETABLE (

'KPI - color',

TOPROWS,

FILTER (

'KPI - color',

MAX('KPI - color'[Tag])),'KPI - color'[Status] ="" || 'KPI - color'[Status] = "In Progress")

RETURN

filteredRowsTable

Re: Dax code for multiple columns cannot be converted to a scalar value

amitchandak — Tue, 05 Apr 2022 14:55:45 GMT

Shruthi96 , You are returning a table, And if you are creating a measure you need to return values like

countrows(filteredRowsTable)

Re: Dax code for multiple columns cannot be converted to a scalar value

Shruthi96 — Tue, 05 Apr 2022 14:59:10 GMT

Thanks amitchandak for reply.. but no luck

Re: Dax code for multiple columns cannot be converted to a scalar value

amitchandak — Tue, 05 Apr 2022 16:18:23 GMT

Shruthi96 , I doubt if will work for tables

VAR TOPROWS = if(countrow,toprows1,TOPROWS2)

Also was at the wrong place here - correct this one and check.

VAR filteredRowsTable =

CALCULATETABLE (

'KPI - color',

TOPROWS,

FILTER (

'KPI - color','KPI - color'[Status] ="" || 'KPI - color'[Status] = "In Progress")

Re: Dax code for multiple columns cannot be converted to a scalar value

Shruthi96 — Wed, 06 Apr 2022 03:35:59 GMT

There is any alternative way to solve?

Re: Dax code for multiple columns cannot be converted to a scalar value

tamerj1 — Wed, 06 Apr 2022 04:22:59 GMT

Hi Shruthi96
You may try

Coummincation = VAR countrow = IF ( CALCULATE ( COUNTROWS ( 'KPI - color' ), 'KPI - color'[Tag] > 0 ), 0, 1 ) VAR TOPROWS1 = GENERATE ( VALUES ( 'KPI - color'[Service] ), VAR mytable = CALCULATETABLE ( TOPN ( 1, 'KPI - color', [Tag], DESC ) ) RETURN SUMMARIZE ( mytable, 'KPI - color'[Tag] ) ) VAR TOPROWS2 = GENERATE ( VALUES ( 'KPI - color'[Service] ), VAR mytable = CALCULATETABLE ( 'KPI - color' ) RETURN SUMMARIZE ( mytable, 'KPI - color'[Tag] ) ) VAR TOPROWS = IF ( countrow, toprows1, TOPROWS2 ) VAR filteredRowsTable = CALCULATETABLE ( 'KPI - color', TOPROWS, FILTER ( 'KPI - color', 'KPI - color'[Status] = "" || 'KPI - color'[Status] = "In Progress" ) ) RETURN MAXX ( filteredRowsTable, 'KPI - color'[Tag] )

Re: Dax code for multiple columns cannot be converted to a scalar value

Shruthi96 — Wed, 06 Apr 2022 05:08:56 GMT

tamerj1 , same error.

Re: Dax code for multiple columns cannot be converted to a scalar value

Icey — Fri, 08 Apr 2022 03:26:40 GMT

Hi Shruthi96 ,

The problem is here:

IF function checks a condition, and returns one value when it's TRUE, otherwise it returns a second value. It can only return a value instead of a table.

In your scenario, try this:

Coummincation = VAR countrow = IF ( CALCULATE ( COUNTROWS ( 'KPI - color' ), 'KPI - color'[Tag] > 0 ), 0, 1 ) VAR TOPROWS1 = GENERATE ( VALUES ( 'KPI - color'[Service] ), VAR mytable = CALCULATETABLE ( TOPN ( 1, 'KPI - color', [Tag], DESC ) ) RETURN SUMMARIZE ( mytable, 'KPI - color'[Tag] ) ) VAR TOPROWS2 = GENERATE ( VALUES ( 'KPI - color'[Service] ), VAR mytable = CALCULATETABLE ( 'KPI - color' ) RETURN SUMMARIZE ( mytable, 'KPI - color'[Tag] ) ) VAR filteredRowsTable1 = CALCULATETABLE ( 'KPI - color', toprows1, FILTER ( 'KPI - color', 'KPI - color'[Status] = "" || 'KPI - color'[Status] = "In Progress" ) ) VAR filteredRowsTable2 = CALCULATETABLE ( 'KPI - color', toprows2, FILTER ( 'KPI - color', 'KPI - color'[Status] = "" || 'KPI - color'[Status] = "In Progress" ) ) RETURN IF ( countrow, CONCATENATEX ( filteredRowsTable1, [Tag], ", " ), CONCATENATEX ( filteredRowsTable2, [Tag], ", " ) )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.