Search first letter word from Slicers and display in card of previous edition

[nagaraj007]

Hello All,

I would require your help on following criteria

1. i have a list of items in slicers (eg. Delhi 2018,Pune 2019,Delhi 2017,Pune 2017)

When i select Pune 2019 in slicer i should get the data of previous year data of Pune only, same with Delhi also. Need to show in Card. 

Please advise.

[v-yulgu-msft · Accepted solution]

Hi @nagaraj007 ,

Suppose the fact data table is called 'Test_2', containing [Column1] and [Column2]. New a calculated table which is unrelated to 'Test_2', to list all available slicer items. 

Slicer table = VALUES(Test_2[Column1])

Add below measure into card.

Measure 2 =
VAR yearNo_selected =
    VALUE ( RIGHT ( SELECTEDVALUE ( 'Slicer table'[Column1] ), 4 ) )
VAR cate_selcted =
    LEFT (
        SELECTEDVALUE ( 'Slicer table'[Column1] ),
        LEN ( SELECTEDVALUE ( 'Slicer table'[Column1] ) ) - 4
    )
RETURN
    CALCULATE (
        SUM ( Test_2[Column2] ),
        FILTER (
            ALLSELECTED ( Test_2 ),
            VALUE ( RIGHT ( Test_2[Column1], 4 ) ) = yearNo_selected - 1
                && LEFT ( Test_2[Column1], LEN ( Test_2[Column1] ) - 4 ) = cate_selcted
        )
    )

Best regards,

Yuliana Gu

[nagaraj007]

Thank you Yuliana