Forum Discussion

KGOLSEN's avatar
KGOLSEN
Frequent Visitor
5 years ago
Solved

Add column with a measure or DAX?

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.

Need Help
  • DataZoe's avatar
    DataZoe
    5 years ago

    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's avatar
    jdbuchanan71
    5 years ago

    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"
)

7 Replies

  • KGOLSEN's avatar
    KGOLSEN
    Frequent Visitor
    5 years ago

    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!

  • jdbuchanan71's avatar
    jdbuchanan71
    Icon for Super User rankSuper User
    5 years ago

    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.

      • jdbuchanan71's avatar
        jdbuchanan71
        Icon for Super User rankSuper User
        5 years ago

        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"
)
  • DataZoe's avatar
    DataZoe
    Icon for Microsoft Employee rankMicrosoft Employee
    5 years ago

    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.