Forum Discussion

FZOU's avatar
FZOU
Helper IV
5 years ago
Solved

Calculated Column

Hi everyone,

I want to create a column named "NEW" from column "OLD" like the picture below! the rules are to take same values from OLD to NEW but with adding new values that group


-If "OLD" begins with A*, then we will have an additional selection as "A" showing all A* . We need to keep each A  as a selection  also !

EXP : 
(A/CAR, A/BUS, A/TRAIN)==>A
(B/CAR, B/BUS, B/TRAIN)==>B
....
The screenshot below could help you understand what i need to do.

-to resume i want to get the column with the name "NEW",i hope this could help

Thank you for your help !

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi FZOU ,

     

    According to my undertanding, you want to display all rows beginning with the first letter of the selected value in slicer, right?

     

    As mentioned above, after creating a new table, please try the following measure:

     

     

    Measure =
IF (
    LEFT ( MAX ( 'Table'[OLD] ), 1 )
        = LEFT ( SELECTEDVALUE ( 'New Table'[New] ), 1 ),
    1,
    0
)

     

     

    And apply it to visual-filter pane, set as "=1" like this:

    Here is the pbix file.

     

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

7 Replies

  • parry2k's avatar
    parry2k
    Super User
    5 years ago

    FZOU not really clear what you are looking for, can you provide more details.

  • FZOU's avatar
    FZOU
    Helper IV
    5 years ago

    Hi parry2k , i want to create the column named "NEW" based on the column named "OLD" on the picture above !
    -If "OLD" begins with A*, then we will have an additional selection as "A" showing all A* . We need to keep each A  as a selection  also !

    -to resume i want to get the column with the name "NEW",i hope this could help

  • parry2k's avatar
    parry2k
    Super User
    5 years ago

    FZOU you need to create a new calculated table, you cannot add rows to the existing table. Let me know if that will work for you. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi FZOU ,

     

    Please use the following formula to create a calculated table:

    New Table =
VAR t1 =
    DISTINCT ( SELECTCOLUMNS ( 'Table', "New", LEFT ( [OLD], 1 ) ) )
VAR t2 =
    DISTINCT ( SELECTCOLUMNS ( 'Table', "New", 'Table'[OLD] ) )
RETURN
    ADDCOLUMNS (
        UNION ( t1, t2 ),
        "OLD", LOOKUPVALUE ( 'Table'[OLD], 'Table'[OLD], [New] )
    )

    The final output is shown below:

    Here is the pbix file.

     

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

    • FZOU's avatar
      FZOU
      Helper IV
      5 years ago

      Hi Anonymous ,thank you for your help,but it didn't do the main goal, because if i use NEW as a slicer and select A, in the other hand a table with OLD  it should display all OLD who start with A, you find below an exemple:
      Thank you !

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi FZOU ,

     

    According to my undertanding, you want to display all rows beginning with the first letter of the selected value in slicer, right?

     

    As mentioned above, after creating a new table, please try the following measure:

     

     

    Measure =
IF (
    LEFT ( MAX ( 'Table'[OLD] ), 1 )
        = LEFT ( SELECTEDVALUE ( 'New Table'[New] ), 1 ),
    1,
    0
)

     

     

    And apply it to visual-filter pane, set as "=1" like this:

    Here is the pbix file.

     

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi FZOU ,

     


    Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it.

     

    Best Regards,
    Eyelyn Qin