Forum Discussion

Moof's avatar
Moof
Frequent Visitor
7 years ago

Using SWITCH in a Filter Context

So I'm trying to create a table that has a YTD and MTD column, based on a measure that filters from a report spec.

 

The table I'm trying to create looks like this (apologies for the Spanish):

 

 

Where Cifra de Negocio is defined as a measure:

 

Cifra de Negocio = 
CALCULATE (
        SUM ( 'Transacciones Control de Gestion'[Valor Contable] ),
        FILTER (
            'Informes',
            'Informes'[Nivel 1] = "CIFRA DE NEGOCIO"
        )
    )

And Cifra de Negocio Año Anterior is defined as the following measure:

 

Cifra De Negocio Año Anterior = 
CALCULATE (
    [Cifra De Negocio],
    SAMEPERIODLASTYEAR ( Fechas[Fecha] )
)

Transacciones Control de Gestión is a filtered table that contains all my transactions. Informes contains a report specification, which maps to a number of different account numbers that are related to Transacciones Control de Gestión. 

 

In the table, Mes and Año are the spanish versions of MTD and YTD.

 

So I try to rewrite Cifra de Negocio as:

 

Cifra De Negocio =
CALCULATE (
    SUM ( 'Transacciones Control de Gestion'[Valor Contable] ),
    FILTER (
        'Informes',
        'Informes'[Nivel 1] = "CIFRA DE NEGOCIO"
    ),
    IF (
        ISFILTERED ( 'Periodos en Curso'[Periodo] ),
        SWITCH (
            VALUES ( 'Periodos en Curso'[Periodo] ),
            "Mes", DATESMTD ( Fechas[Fecha] ),
            "Año", DATESYTD ( Fechas[Fecha] ),
            ALLSELECTED ( Fechas[Fecha] )
        ),
        ALLSELECTED ( Fechas[Fecha] )
    )
)

But I get the error "A function 'DATESYTD' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

 

I've eventually ended up replacing that measure with:

 

Cifra De Negocio =
IF (
    ISFILTERED ( 'Periodos en Curso'[Periodo] ),
    SWITCH (
        VALUES ( 'Periodos en Curso'[Periodo] ),
        "Mes", TOTALMTD (
            SUM ( 'Transacciones Control de Gestion'[Valor Contable] ),
            Fechas[Fecha],
            FILTER (
                'Informes',
                'Informes'[Nivel 1] = "CIFRA DE NEGOCIO"
            )
        ),
        "Año", TOTALYTD (
            SUM ( 'Transacciones Control de Gestion'[Valor Contable] ),
            Fechas[Fecha],
            FILTER (
                'Informes',
                'Informes'[Nivel 1] = "CIFRA DE NEGOCIO"
            )
        )
    ),
    CALCULATE (
        SUM ( 'Transacciones Control de Gestion'[Valor Contable] ),
        ALLSELECTED ( Fechas[Fecha] ),
        FILTER (
            'Informes',
            'Informes'[Nivel 1] = "CIFRA DE NEGOCIO"
        )
    )
)

Which seems, at best, a very long-winded way of doing things.

 

My question is twofold:

  • Why can I not use a logical function to choose a flow control function? Is there a way to encapsulate the filter, or delay evaluation until the SWITCH or IF is evaluated in the row context?
  • What is the best way to reduce my current approach so that it's less repetitive and more readable?

 

Need Help