Add column with a measure or DAX?

[KGOLSEN]

I have a highly irritating problem. I am not able to "Get Data" from other sources to add a column to a model that I use. All I would like to do is basically perform a lookup to add a Group Name to a unique account identification number. Is this at all possible using a measure or DAX? Ex. IF ID = 1234 THEN 'GROUP NAME' = ACME INC. Please and thank you.

[DataZoe · Accepted solution]

Hi @KGOLSEN ,

i am assuming you can't also add a calculated column as it's connected to another dataset. If you can create a calculated column then it's better you group there.

You can create a measure to do what you want, but you will not be able to use it in a slicer (though you can use it to filter an individual visualation in the filter pane) or on any axis/legend. I have set up this scenario before this way:

1. Create a measure to sort the column into groups

Group Measure =
IF (
    SELECTEDVALUE ( Table[ID] ) IN { 123, 456, 789 },
    "Group A",
    IF ( SELECTEDVALUE ( Table[ID] ) IN { 1, 2, 3, 4 }, "Group B", "Group C" )
)

1. And at this point you realize you can't use it as an axis. So you have to make each measure filtered.

[Sales Group A] =
CALCULATE ( [Sales], FILTER ( Table, [Group Measure] = "Group A" ) )

Then you can add them each in on the values section of a visual.

[KGOLSEN]

I was informed that what I want to do just isn't possible and the work around is just too much effort. Thank you for the suggestions and I will definetly save them as I am sure they will come in handy in the future!

[DataZoe · Accepted solution]

Hi @KGOLSEN ,

i am assuming you can't also add a calculated column as it's connected to another dataset. If you can create a calculated column then it's better you group there.

You can create a measure to do what you want, but you will not be able to use it in a slicer (though you can use it to filter an individual visualation in the filter pane) or on any axis/legend. I have set up this scenario before this way:

1. Create a measure to sort the column into groups

Group Measure =
IF (
    SELECTEDVALUE ( Table[ID] ) IN { 123, 456, 789 },
    "Group A",
    IF ( SELECTEDVALUE ( Table[ID] ) IN { 1, 2, 3, 4 }, "Group B", "Group C" )
)

1. And at this point you realize you can't use it as an axis. So you have to make each measure filtered.

[Sales Group A] =
CALCULATE ( [Sales], FILTER ( Table, [Group Measure] = "Group A" ) )

Then you can add them each in on the values section of a visual.

[jdbuchanan71]

Do you have the ID / Name pair somewhere else in your model that you can use LOOKUPVALUE to pull from?  It does not have to be joined to your destination table.

[KGOLSEN]

Unfortunately not. 

[jdbuchanan71 · Accepted solution]

The cleanest I can think of then is using SWITCH, something like this.

Column = 
SWITCH(
    'Table'[ID],
    1234,"Group 2",
    2345,"Group 3",
    3456,"Group 4",
    4567,"Group 5",
    5678,"Group 6",
    6789,"Group 7",
    7900,"Group 8",
    9011,"Group 9"
)

[KGOLSEN]

And the 'Table' [ID] could be from an external source yes?

[jdbuchanan71]

I thought you were trying to add a column to an existing table that already had the [ID] field in it?