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

[Shruthi96]

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

[Icey · Accepted solution]

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.

[tamerj1]

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] )

[Shruthi96]

@tamerj1 , same error. 

[Shruthi96]

There is any alternative way to solve? 

[Shruthi96]

Thanks @amitchandak for reply.. but no luck

[amitchandak]

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

countrows(filteredRowsTable)

[amitchandak]

@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")