Adding custom column to query table

[Powell360]

I have a table in an SQL DB which contains months represented in text and as 2 different integers (Calendar sorting and Financial Year sorting). I have been able to pull it into Power BI Desktop but now I want to add a Dynamic sort column based on the current month (IF statement below). This worked absolutely fine until I got a new laptop yesterday and now suddenly I get "Case expressions may only be nested to level 10.

Im not doing a case statement! Im running an IF in PowerBI after the data has been retrieved from SQL so why is it trying to run it all on the SQL server?

if([CallendarSort]=Date.Month(DateTime.LocalNow())) then(12) else(
if([CallendarSort]=Date.Month(DateTime.LocalNow())-1) then(11) else(
if([CallendarSort]=Date.Month(DateTime.LocalNow())-2) then(10) else(
if([CallendarSort]=Date.Month(DateTime.LocalNow())-3) then(9) else(
if([CallendarSort]=Date.Month(DateTime.LocalNow())-4) then(8) else(
if([CallendarSort]=Date.Month(DateTime.LocalNow())-5) then(7) else(
if([CallendarSort]=Date.Month(DateTime.LocalNow())-6) then(6) else(
if([CallendarSort]=Date.Month(DateTime.LocalNow())-7) then(5) else(
if([CallendarSort]=Date.Month(DateTime.LocalNow())-8) then(4) else(
if([CallendarSort]=Date.Month(DateTime.LocalNow())-9) then(3) else(
if([CallendarSort]=Date.Month(DateTime.LocalNow())-10) then(2) else(
if([CallendarSort]=Date.Month(DateTime.LocalNow())-11) then(1) else(
if([CallendarSort]=Date.Month(DateTime.LocalNow())+1) then(1)else(
if([CallendarSort]=Date.Month(DateTime.LocalNow())+2) then(2)else(
if([CallendarSort]=Date.Month(DateTime.LocalNow())+3) then(3)else(
if([CallendarSort]=Date.Month(DateTime.LocalNow())+4) then(4)else(
if([CallendarSort]=Date.Month(DateTime.LocalNow())+5) then(5)else(
if([CallendarSort]=Date.Month(DateTime.LocalNow())+6) then(6)else(
if([CallendarSort]=Date.Month(DateTime.LocalNow())+7) then(7)else(
if([CallendarSort]=Date.Month(DateTime.LocalNow())+8) then(8)else(
if([CallendarSort]=Date.Month(DateTime.LocalNow())+9) then(9)else(
if([CallendarSort]=Date.Month(DateTime.LocalNow())+10) then(10)else(
if([CallendarSort]=Date.Month(DateTime.LocalNow())+11) then(11)else(
0)))))))))))))))))))))))

Any idea or workarounds?

Thanks in advance 

[v-shex-msft]

HI @Powell360,

>>Im not doing a case statement! Im running an IF in PowerBI after the data has been retrieved from SQL so why is it trying to run it all on the SQL server?

It seems like if statement condition nested amount has over the limit. In my opinion, I'd like to suggest you use dax formula, it not contains the 'case' limit.

Sample:

Check (if statement)=
VAR _current =
    MONTH ( TODAY () )
RETURN
    IF (
        AND ( [CallendarSort] <= _current, [CallendarSort] > _current - 12 ),
        12
            - ( _current - [CallendarSort] ),
        IF (
            AND ( [CallendarSort] > _current, [CallendarSort] < _current + 12 ),
            [CallendarSort] - _current,
            0
        )
    )


Check (switch)=
VAR _current =
    MONTH ( TODAY () )
RETURN
    SWITCH (
        [CallendarSort],
        _current, 12,
        _current - 1, 11,
        _current - 2, 10,
        _current - 3, 9,
        _current - 4, 8,
        _current - 5, 7,
        _current - 6, 6,
        _current - 7, 5,
        _current - 8, 4,
        _current - 9, 3,
        _current - 10, 2,
        _current - 11, 1,
        _current + 1, 1,
        _current + 2, 2,
        _current + 3, 3,
        _current + 4, 4,
        _current + 5, 5,
        _current + 6, 6,
        _current + 7, 7,
        _current + 8, 8,
        _current + 9, 9,
        _current + 10, 10,
        _current + 11, 11,
        0
    )

If you had to use power query formula, you can refer to below formula, I have summarized your conditions formula.

Full query:

 #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each Function.Invoke((id as number,month as number ) as number => 
if (id <=month) and (id > month-12) then (12-(month-id)) else if (id > month) and (id <month+12) then (id-month) else 0,{[CallendarSort],Date.Month(DateTime.LocalNow())}))

Regards,

Xiaoxin Sheng

[Powell360]

Thanks for the reply.

My question is why is it being converted and expressed as a case statement at all? 

[v-shex-msft]

HI @Powell360,

No, it hasn't converted. 'if... else...' query is a condition statement and power query has limit on subsidiary/branch condition query.

The error message only means subsidiary/branch conditions query cannot be expand more than 10.

Regards,

Xiaoxin Sheng

[Powell360]

@v-shex-msft thanks for the reply. 

The syntax used to create the column is DAX, the error message references a case statement and when I remove the IF, the problem CASE is solved so I'd say a conversion is happening somewhere