Calculated Column

[FZOU]

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 !

[Anonymous]

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

[Anonymous · Accepted solution]

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]

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]

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 !

[parry2k]

@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. 

[FZOU]

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]

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