Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
2 years ago
Solved

error in DAX formula when segmenting production poor mes

Good afternoon

I have this DAX formula to segment production by month:

MesF = SWITCH( TRUE(),
Table1[DATE] > DATE(2023,1,20& Tabla1 < DATE(2023,2,21), "February2023",
Table1[DATE] > DATE(2023,2,20& Tabla1 < DATE(2023,3,21), "March 2023",
Table1[DATE] > DATE(2023,3,20& Tabla1 < DATE(2023,4,21), "April 2023",
Table1[DATE] > DATE(2023,4,20& Tabla1 < DATE(2023,5,21), "May2023",
Table1[DATE] > DATE(2023,5,20& Tabla1 < DATE(2023,6,21), "June2023",
Table1[DATE] > DATE(2023,6,20& Tabla1 < DATE(2023,7,21), "July2023",
Table1[DATE] > DATE(2023,7,20& Tabla1 < DATE(2023,8,21), "August 2023",
Table1[DATE] > DATE(2023,8,20& Tabla1 < DATE(2023,9,21), "September 2023",
Table1[DATE] > DATE(2023,9,20& Tabla1 < DATE(2023,10,21), "October 2023",
Table1[DATE] > DATE(2023,10,20& Tabla1 < DATE(2023,11,21), "November2023",
Table1[DATE] > DATE(2023,11,20& Tabla1 < DATE(2023,12,21), "December 2023",
Table1[DATE] > DATE(2023,12,20& Tabla1 < DATE(2024,1,21), "January2024",
Table1[DATE] > DATE(2024,1,20& Tabla1 < DATE(2024,2,21), "February2024",
Table1[DATE] > DATE(2024,2,20& Tabla1 < DATE(2024,3,21), "March2024",
"Out of Range")
But it gives me the following error:
The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.
Thank you in advance for any help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Syndicate_Admin ,

     

    You can modify your formula like below:

    MesF =
SWITCH (
    TRUE (),
    MAX ( Table1[DATE] ) > DATE ( 2023, 1, 20 )
        && MAX ( Table1[DATE] ) < DATE ( 2023, 2, 21 ), "February2023",
    MAX ( Table1[DATE] ) > DATE ( 2023, 2, 20 )
        && MAX ( Table1[DATE] ) < DATE ( 2023, 3, 21 ), "March 2023",
    "Out of Range"
)

     

    Best Regards,
    Adamk Kong

     

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

3 Replies

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Icon for Administrator rankAdministrator
    2 years ago

    I already saw the error, after publishing the post. The condition of minor referred to the entire table1 and had to be dated.

    Thank you

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    2 years ago

    Hi,

    Just create a Calendar Table with calculated column formulas for Year, Month name and Month number.  Sort the Month name by the Month number.  Create a relationship from the Date column of Table1 to the Date column of the Calendar Table.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Syndicate_Admin ,

     

    You can modify your formula like below:

    MesF =
SWITCH (
    TRUE (),
    MAX ( Table1[DATE] ) > DATE ( 2023, 1, 20 )
        && MAX ( Table1[DATE] ) < DATE ( 2023, 2, 21 ), "February2023",
    MAX ( Table1[DATE] ) > DATE ( 2023, 2, 20 )
        && MAX ( Table1[DATE] ) < DATE ( 2023, 3, 21 ), "March 2023",
    "Out of Range"
)

     

    Best Regards,
    Adamk Kong

     

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