Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Build a string from a filter selection

Hi,

 

I am trying to build a string every time I make a selectiong from a filter I added from a table I added see the table below

 

The month the month that is selected in the filter must return the previous month string

e.g If the selected value is May the string must return APRFCST
If the selected value is Jun the string must return MAYFCST

If the selected value is Jul the string must return JULFCST

 

Month NumMonth Short
Apr1
May2
Jun3
Jul4
Aug5
Sep6
Oct4
Nov8
Dec9

 

General Comment
Need Help
Tips and Tricks
  • v-xulin-mstf's avatar
    v-xulin-mstf
    4 years ago

    Hi Anonymous,

     

    Create slicer table as:

    Slicer = 
VALUES('Table'[Month Num])

    Create column as:

    Column = 
CALCULATE(
    MAX('Table'[Month Num]),
    FILTER(
        'Table',
        'Table'[Month Short]=EARLIER('Table'[Month Short])-1
        )
    )

    Create measure as:

    Measure = 
var _month=
CALCULATE(
    MAX('Table'[Column]),
    FILTER(
        'Table',
        'Table'[Month Num]=ALLSELECTED(Slicer[Month Num])
    )
)
return
CONCATENATE(_month,"FCST")

    Here is the output:

    The demo is attached, please try it.

     

    If you still have some question, please don't hesitate to let me known.‌‌

     

    Best Regards,

    Link

     

    Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    The below is what I am trying to achieve:

    Forecast =
     
    VAR FCST = ???? ****I need to build the string here****
     

    RETURN
     
    CALCULATE( Sum('ABF'[Amount]),
    ALL('Forecast List'),
    'ABF'[Category]=FCST )
     
  • v-xulin-mstf's avatar
    v-xulin-mstf
    Community Support
    4 years ago

    Hi Anonymous,

     

    Create slicer table as:

    Slicer = 
VALUES('Table'[Month Num])

    Create column as:

    Column = 
CALCULATE(
    MAX('Table'[Month Num]),
    FILTER(
        'Table',
        'Table'[Month Short]=EARLIER('Table'[Month Short])-1
        )
    )

    Create measure as:

    Measure = 
var _month=
CALCULATE(
    MAX('Table'[Column]),
    FILTER(
        'Table',
        'Table'[Month Num]=ALLSELECTED(Slicer[Month Num])
    )
)
return
CONCATENATE(_month,"FCST")

    Here is the output:

    The demo is attached, please try it.

     

    If you still have some question, please don't hesitate to let me known.‌‌

     

    Best Regards,

    Link

     

    Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!