Help with Switch function

[Boycie92]

Hi,

I am hoping someone can help.

I Want to create a Switch column. I have this formula:

DayRange =

SWITCH (

   TRUE (),

   Table[Date] <> BLANK ()

       &&    Table[Date1] <> BLANK(), "Other",

      Table[Date] <> BLANK ()

       && [NoOfDays] > 365, "> 12 Months",

      Table[Date] <> BLANK ()

       && [NoOfDays] > 274, "> 9 Months",

      Table[Date] <> BLANK ()

       && [NoOfDays] > 183, "> 6 Months",

      Table[Date] <> BLANK ()

       && [NoOfDays] > 91, "> 3 Months",

      Table[Date] <> BLANK ()

       && [NoOfDays] <= 91, "< 3 Months",

   " N/A"

)

I am getting this error: Function 'CALCULATE' is not allowed as part of calculated column DAX expressions on DirectQuery models.

I have tried using SWITCH as a measure, but it only shows one citatory. Can anyone get this to work?

Thanks in advance,

Boycie92

[Anonymous]

Hi @Boycie92,

Maybe you can try to use below formula:

DayRange =
IF (
    Table[Date] = BLANK (),
    " N/A",
    IF (
        [NoOfDays] > 365,
        "> 12 Months",
        IF (
            [NoOfDays] > 274,
            "> 9 Months",
            IF (
                [NoOfDays] > 183,
                "> 6 Months",
                IF (
                    [NoOfDays] > 91,
                    "> 3 Months",
                    IF ( [NoOfDays] <= 91, "< 3 Months", IF ( Table[Date1] <> BLANK (), "Other" ) )
                )
            )
        )
    )
)

Regards,

Xiaoxin Sheng

[Boycie92]

Hi @Anonymous

thanks for the help.

when I use the formula in a column I still get the CALCULATE error.

when I use it as a measure its says "A single value for column 'TableDate' in table 'Table' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

Any idea why?

Thanks

Boycie92

[Anonymous]

Hi @Boycie92,

Please provide pbix file for further test.

Regards,
Xiaoxin Sheng