Forum Discussion

Shruthi96's avatar
Shruthi96
Icon for Helper III rankHelper III
4 years ago
Solved

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

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's avatar
    Icey
    4 years ago

    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.

7 Replies

  • Icey's avatar
    Icey
    Icon for Community Support rankCommunity Support
    4 years ago

    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.

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

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

     

    countrows(filteredRowsTable)

    • amitchandak's avatar
      amitchandak
      Icon for Super User rankSuper User
      4 years ago

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

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

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