Forum Discussion

PhilSmith's avatar
PhilSmith
Helper III
1 year ago

How to Calculated Column with Switch() and IN()

Hey All,

I think looking at this should tell you what I am trying to get at in a calculated Column. 

Account# = Value(Account[Account]) is the relevant calculated column.
I am getting this error:  The syntax for 'Account' is incorrect. (DAX(SWITCH( TRUE,'Account[Account#] ...

 

MarketingAccounts2 = SWITCH( TRUE,

'Account[Account#] in {4400,4495,4496,4497,4499},"SalesGL",
'Account[Account#] in {7000,7005,7010,7001,7002}, "ADVERTISING_EXPENSES",
'Account[Account#] in {7020,7025,7030,7035,7040,7045,7048}, "CONSUMER_MKTG_AND_SPONSORSHIPS",
'Account[Account#] in {7140,7145,7150,7155,7160,7180,7182,7185,7190,7195,7200}, "TEAM_PUBLICITY_AND_TRAVEL",
'Account[Account#] in {7125,7165,7170,7175}, "TEAM_SALARIES_AND_INCENTIVES",
'Account[Account#] in {7021,7085,7090,7130,7135,8170,8175,8176,8217,8218}, "PROMO",
'Account[Account#] in {7015,7016}, "VIDEO_EXPENSES_AND_SPONSORSHIPS","")
 
What am I missing here?
Phil
 
Thanx
 
Need Help

5 Replies

  • MFelix's avatar
    MFelix
    Super User
    1 year ago

    Hi PhilSmith ,

     

    You are missing a parentesis in True and closing the Table name with single quote:

     

    MarketingAccounts2 =
SWITCH (
    TRUE (),
    'Account'[Account#] IN { 4400, 4495, 4496, 4497, 4499 }, "SalesGL",
    'Account'[Account#] IN { 7000, 7005, 7010, 7001, 7002 }, "ADVERTISING_EXPENSES",
    'Account'[Account#] IN { 7020, 7025, 7030, 7035, 7040, 7045, 7048 }, "CONSUMER_MKTG_AND_SPONSORSHIPS",
    'Account'[Account#]
        IN { 7140, 7145, 7150, 7155, 7160, 7180, 7182, 7185, 7190, 7195, 7200 }, "TEAM_PUBLICITY_AND_TRAVEL",
    'Account'[Account#] IN { 7125, 7165, 7170, 7175 }, "TEAM_SALARIES_AND_INCENTIVES",
    'Account'[Account#]
        IN { 7021, 7085, 7090, 7130, 7135, 8170, 8175, 8176, 8217, 8218 }, "PROMO",
    'Account'[Account#] IN { 7015, 7016 }, "VIDEO_EXPENSES_AND_SPONSORSHIPS",
    ""
)

     

    This should work

    • PhilSmith's avatar
      PhilSmith
      Helper III
      1 year ago

      Thanx Miguel,

      but something is still off.  (I see the issues with table not being quoted on both sides was mine, but the example of using True in Switch I found did not have true in parens.)

      Now I get the error: A single value for column 'Account#' in table account cannot be determined...

      This is a calculated column, derived from another column in the same table,  and:

      so I am not sure what the problem might be.

       

      Phil

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi PhilSmith ,

         

        Please double-check the column name and the data type.Ensure that 'Account'[Account#] is the correct column name and matches exactly as it appears in your data model.
        Verify that the Account# column is numeric. If it’s stored as text, you may need to convert it using VALUE('Account'[Account#]).

         

        Best Regards,
        Stephen Tao

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