Forum Discussion

matrix_user's avatar
matrix_user
Helper III
4 years ago
Solved

Calculated column & DAX for slicer issues.

Hi,

 

Is there anyone who can help? I am trying to work out how I could create the 'Desc' column below so that it can be used in a slicer. The fields pane shows that the 'Desc' column is somewhat a column created by DAX in a calculated column. I am assuming this because there is no SUM symbol like Index Dexc and Index has.

 

The 'Desc' column has been created from the 'Period' column.  For example, 'W1 2019' would be selected as 'W' in the slicer. '2021' would be 'Y' and 'Jan 2019' would be 'M'. See pic below.

 

 

The above pic are not from my pbix file I am working on. They are from online. I am trying to replicate this:

 

 

Thank you.

Help with Slicer.
  • v-robertq-msft's avatar
    v-robertq-msft
    4 years ago

    Hi, 

    According to your description, I can roughly understand your requirement, I think you can try this DAX formula to achieve your requirement:

    You can create the calculated column [Desc] like this:

    Desc =

IF(

    NOT(ISERROR(VALUE(LEFT('Slicer'[Period]))+1)),"Y",

    IF(

        NOT(LEFT('Slicer'[Period],1) in {"Q","W"}),"M",

        LEFT('Slicer'[Period],1)))

     

    And you can get what you want, like this:

     

    You can download my test pbix file below

    Thank you very much!

     

    Best Regards,

    Community Support Team _Robert Qin

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

3 Replies

  • VahidDM's avatar
    VahidDM
    Super User
    4 years ago

    Hi matrix_user 

     

    Can you share a sample of your data table in a text format?

     

     

    BTW, try this code to add a new column with dax:

     

    New Column =
VAR _RW =
    RIGHT ( Table[Period], 1 )
RETURN
    IF ( _RW = "W", "W", IF ( _RW = 2, "Y", "M" ) )




     

    If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
    Appreciate your Kudos!!
    LinkedIn:     www.linkedin.com/in/vahid-dm/

     

     

  • v-robertq-msft's avatar
    v-robertq-msft
    Community Support
    4 years ago

    Hi, 

    According to your description, I can roughly understand your requirement, I think you can try this DAX formula to achieve your requirement:

    You can create the calculated column [Desc] like this:

    Desc =

IF(

    NOT(ISERROR(VALUE(LEFT('Slicer'[Period]))+1)),"Y",

    IF(

        NOT(LEFT('Slicer'[Period],1) in {"Q","W"}),"M",

        LEFT('Slicer'[Period],1)))

     

    And you can get what you want, like this:

     

    You can download my test pbix file below

    Thank you very much!

     

    Best Regards,

    Community Support Team _Robert Qin

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