Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MojoGene
Post Patron
Post Patron

SWITCH con múltiples criterios

He estado luchando con esto por un tiempo, pero no puedo progresar.

La organización tiene asuntos que se facturan mensualmente, bimensuales o trimestrales, por lo que el 1 de cada mes determinado todos los asuntos que cumplen con los criterios deben ser facturados.

La frecuencia de la factura se establece en una tabla Matters en un campo llamado BillFrq donde los valores aceptables son "Monthly", "B1", "B2", "Q1", "Q2" o "Q3" donde "Mensual" es evidente, "B1" indica asuntos facturados en meses de número impar, "B2" indica asuntos facturados en meses de número par, "Q1" indica asuntos facturados trimestralmente en enero/abril/julio/octubre, "Q2" indica asuntos facturados trimestralmente en feb/mayo/agosto y "Q3" indica asuntos facturados trimestralmente en marzo/julio/s.p., alto.

Estoy tratando de crear una medida que identifique todos los Asuntos adeudados para la facturación en un mes dado, por ejemplo, todos los asuntos adeudados para la facturación en enero, o, alternativamente, o todos los asuntos adeudados para la facturación en el mes actual.

He intentado esto, pero obtengo un error que switch no se puede utilizar para comparar True/False y funciones de texto.

BillFrq = 
SWITCH (
    CONTAINS ( Matters, Matters[BillFreq], "Monthly" ),
    "Due for billing this month", CONTAINS ( Matters, Matters[BillFreq], "B1" )
        && MONTH ( DATE ( TODAY (), TODAY (), TODAY () ) ) = "1"
        || MONTH ( DATE ( TODAY (), TODAY (), TODAY () ) ) = "3"
        || MONTH ( DATE ( TODAY (), TODAY (), TODAY () ) ) = "5"
        || MONTH ( DATE ( TODAY (), TODAY (), TODAY () ) ) = "7"
        || MONTH ( DATE ( TODAY (), TODAY (), TODAY () ) ) = "9"
        || MONTH ( DATE ( TODAY (), TODAY (), TODAY () ) ) = "11",
    "Due for billing this month", CONTAINS ( Matters, Matters[BillFreq], "B2" )
        && MONTH ( DATE ( TODAY (), TODAY (), TODAY () ) ) = "2"
        || MONTH ( DATE ( TODAY (), TODAY (), TODAY () ) ) = "4"
        || MONTH ( DATE ( TODAY (), TODAY (), TODAY () ) ) = "6"
        || MONTH ( DATE ( TODAY (), TODAY (), TODAY () ) ) = "8"
        || MONTH ( DATE ( TODAY (), TODAY (), TODAY () ) ) = "10"
        || MONTH ( DATE ( TODAY (), TODAY (), TODAY () ) ) = "12",
    "Due for billing this month", CONTAINS ( Matters, Matters[BillFreq], "Q1" )
        && MONTH ( DATE ( TODAY (), TODAY (), TODAY () ) ) = "1"
        || MONTH ( DATE ( TODAY (), TODAY (), TODAY () ) ) = "4"
        || MONTH ( DATE ( TODAY (), TODAY (), TODAY () ) ) = "7"
        || MONTH ( DATE ( TODAY (), TODAY (), TODAY () ) ) = "10",
    "Due for billing this month", CONTAINS ( Matters, Matters[BillFreq], "Q2" )
        && MONTH ( DATE ( TODAY (), TODAY (), TODAY () ) ) = "2"
        || MONTH ( DATE ( TODAY (), TODAY (), TODAY () ) ) = "5"
        || MONTH ( DATE ( TODAY (), TODAY (), TODAY () ) ) = "8"
        || MONTH ( DATE ( TODAY (), TODAY (), TODAY () ) ) = "11",
    "Due for billing this month", CONTAINS ( Matters, Matters[BillFreq], "Q3" )
        && MONTH ( DATE ( TODAY (), TODAY (), TODAY () ) ) = "3"
        || MONTH ( DATE ( TODAY (), TODAY (), TODAY () ) ) = "5"
        || MONTH ( DATE ( TODAY (), TODAY (), TODAY () ) ) = "8"
        || MONTH ( DATE ( TODAY (), TODAY (), TODAY () ) ) = "12",
    "Due for billing this month", "Not due for billing"
)

Cualquier ayuda apreciada.

1 ACCEPTED SOLUTION

La fórmula FECHA pide:
Año, MES y DIA que siempre estás poniendo hoy no te neeed que.

Reemplace todo el MES ( FECHA (HOY(), HOY(), HOY())) simplemente

MES (HOY())

También para simplemente medir probar el siguiente código:

BillFrq =
VAR Month_Selection =
    MONTH ( TODAY () )
RETURN
    SWITCH (
        TRUE (),
        CONTAINS ( Matters, Matters[BillFreq], "Monthly" ), "Due for billing this month",
        CONTAINS ( Matters, Matters[BillFreq], "B1" )
            && Month_Selection IN { 1, 3, 5, 7, 9, 11 }, "Due for billing this month",
        CONTAINS ( Matters, Matters[BillFreq], "B2" )
            && Month_Selection IN { 2, 4, 6, 8, 10, 12 }, "Due for billing this month",
        CONTAINS ( Matters, Matters[BillFreq], "Q1" )
            && Month_Selection IN { 1, 4, 7, 10 }, "Due for billing this month",
        CONTAINS ( Matters, Matters[BillFreq], "Q2" )
            && Month_Selection IN { 2, 5, 8, 11 }, "Due for billing this month",
        CONTAINS ( Matters, Matters[BillFreq], "Q3" )
            && Month_Selection IN { 3, 5, 8, 12 }, "Due for billing this month",
        "Not due for billing"
    )

Compruebe también los meses dentro de cada uno de los


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

7 REPLIES 7
MFelix
Super User
Super User

Hola @MojoGene ,

El primer parámetro de un switch funcion es la expresión por lo que el valor que desea comparar alonside con el resto de los valores en su caso lo está haciendo :

CONTAINS ( Matters, Matters[BillFreq], "Monthly" )

Y luego usted está colocando el

"Due for billing this month"

En este caso, usted está haciendo creer a la comparación que la segunda parte es lo que desea devolver no comparar por lo que necesita rehacer su medida a algo similar a esto.

BillFrq =
SWITCH (
    TRUE (),
    CONTAINS ( Matters, Matters[BillFreq], "Monthly" ), "Due for billing this month",
    CONTAINS ( Matters, Matters[BillFreq], "B1" )
        && MONTH ( DATE ( TODAY (), TODAY (), TODAY () ) ) = "1"
        || MONTH ( DATE ( TODAY (), TODAY (), TODAY () ) ) = "3"
        || MONTH ( DATE ( TODAY (), TODAY (), TODAY () ) ) = "5"
        || MONTH ( DATE ( TODAY (), TODAY (), TODAY () ) ) = "7"
        || MONTH ( DATE ( TODAY (), TODAY (), TODAY () ) ) = "9"
        || MONTH ( DATE ( TODAY (), TODAY (), TODAY () ) ) = "11", "Due for billing this month",
    CONTAINS ( Matters, Matters[BillFreq], "B2" )
        && MONTH ( DATE ( TODAY (), TODAY (), TODAY () ) ) = "2"
        || MONTH ( DATE ( TODAY (), TODAY (), TODAY () ) ) = "4"
        || MONTH ( DATE ( TODAY (), TODAY (), TODAY () ) ) = "6"
        || MONTH ( DATE ( TODAY (), TODAY (), TODAY () ) ) = "8"
        || MONTH ( DATE ( TODAY (), TODAY (), TODAY () ) ) = "10"
        || MONTH ( DATE ( TODAY (), TODAY (), TODAY () ) ) = "12", "Due for billing this month",
    CONTAINS ( Matters, Matters[BillFreq], "Q1" )
        && MONTH ( DATE ( TODAY (), TODAY (), TODAY () ) ) = "1"
        || MONTH ( DATE ( TODAY (), TODAY (), TODAY () ) ) = "4"
        || MONTH ( DATE ( TODAY (), TODAY (), TODAY () ) ) = "7"
        || MONTH ( DATE ( TODAY (), TODAY (), TODAY () ) ) = "10", "Due for billing this month",
    CONTAINS ( Matters, Matters[BillFreq], "Q2" )
        && MONTH ( DATE ( TODAY (), TODAY (), TODAY () ) ) = "2"
        || MONTH ( DATE ( TODAY (), TODAY (), TODAY () ) ) = "5"
        || MONTH ( DATE ( TODAY (), TODAY (), TODAY () ) ) = "8"
        || MONTH ( DATE ( TODAY (), TODAY (), TODAY () ) ) = "11", "Due for billing this month",
    CONTAINS ( Matters, Matters[BillFreq], "Q3" )
        && MONTH ( DATE ( TODAY (), TODAY (), TODAY () ) ) = "3"
        || MONTH ( DATE ( TODAY (), TODAY (), TODAY () ) ) = "5"
        || MONTH ( DATE ( TODAY (), TODAY (), TODAY () ) ) = "8"
        || MONTH ( DATE ( TODAY (), TODAY (), TODAY () ) ) = "12", "Due for billing this month",
    "Not due for billing"
)


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Miguel:

Gracias por la ayuda, pero aquí está el error que estoy recibiendo con su sugerencia:

Error.png

Usted está calculando meses por lo que ypu necesita agregar números tomar el "" de los números que tiene en su fórmula.

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Miguel:

Solucioné ese problema, pero aún así tengo esto:

Error2.png

La fórmula FECHA pide:
Año, MES y DIA que siempre estás poniendo hoy no te neeed que.

Reemplace todo el MES ( FECHA (HOY(), HOY(), HOY())) simplemente

MES (HOY())

También para simplemente medir probar el siguiente código:

BillFrq =
VAR Month_Selection =
    MONTH ( TODAY () )
RETURN
    SWITCH (
        TRUE (),
        CONTAINS ( Matters, Matters[BillFreq], "Monthly" ), "Due for billing this month",
        CONTAINS ( Matters, Matters[BillFreq], "B1" )
            && Month_Selection IN { 1, 3, 5, 7, 9, 11 }, "Due for billing this month",
        CONTAINS ( Matters, Matters[BillFreq], "B2" )
            && Month_Selection IN { 2, 4, 6, 8, 10, 12 }, "Due for billing this month",
        CONTAINS ( Matters, Matters[BillFreq], "Q1" )
            && Month_Selection IN { 1, 4, 7, 10 }, "Due for billing this month",
        CONTAINS ( Matters, Matters[BillFreq], "Q2" )
            && Month_Selection IN { 2, 5, 8, 11 }, "Due for billing this month",
        CONTAINS ( Matters, Matters[BillFreq], "Q3" )
            && Month_Selection IN { 3, 5, 8, 12 }, "Due for billing this month",
        "Not due for billing"
    )

Compruebe también los meses dentro de cada uno de los


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Miguel:

Probé tu código y funcionó perfectamente.

Gracias, lo he aceptado como solución.

Estar bien, amigo.

g

Miguel

He reemplazado esos valores MONTH, pero aún así obtención del error de comparación TRUE/FALSE frente a texto:

Error2.png

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors