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" begi...
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.

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 !