Dynamic Selection of Slicer for Character Values

[Anonymous]

Hi,

So, I have a power bi report where I have a slicer with values such as :

202005

202004

202003

202002

202001

Pacing

Every month, the data gets added and the category is incremented by 1. i.e in June, I'll have 202006 as well.

I have a graph where I want the data to be shown for last 3 months and Pacing by default.

i.e in May, default selection should be 202004, 202003, 202002, Pacing

in June, default selection should be 202005,202004,202003, Pacing

In other words, I want default selection of : max month-1, max month -2, max month -2 and pacing in the associated graphs.

Furthermore, I should have the flexibility to choose any month to update the view as well.

Also.  I need to have all the data in the report but I need to make the graphs with a default view for few months. i.e if I have months : from 201909 to 202005. I need to show the graph for top few months but still I need to have the flexibility to update the graph for 201909. I hope this makes my question more clear? 

Currently, I am manually making the selection and saving the report and then publishing it again. But to do so, I have to refresh data everytime to get updated values of the month and it has increased my file size to a great extent because the data is imported in my report. I believe that my report can crash anytime if I continue this. Hence, I need urgent help.

I am looking forward to receiving some solution for it.

Thanks,

Sunaina Khera

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

Test(a calculated table):

Test = DISTINCT('Table'[YearMonth])

There is no relationship between tables.

You may create a calculated column and  two measures as below.

Calculated column:
YearMonth = VALUE(FORMAT('Table'[Date],"yyyymm"))

Measure:
Rank = 
RANKX(
    ALL('Table'),
    CALCULATE(MAX('Table'[YearMonth]))
)
Visual control = 
var tab = 
TOPN(
            3,
            FILTER(
            ALL('Table'),
            'Table'[YearMonth]<SELECTEDVALUE(Test[YearMonth])
            ),
            'Table'[YearMonth]
)
var _min = 
MINX(
    tab,
    [YearMonth]
)
var _max = 
MAXX(
    tab,
    'Table'[YearMonth]
)
return
IF(
    NOT(ISFILTERED(Test[YearMonth])),
    IF(
        [Rank]>=1&&[Rank]<=3,
        1,0
    ),
    IF(
        HASONEVALUE(Test[YearMonth]),
        IF(
            SELECTEDVALUE('Table'[YearMonth]) >=_min&&
            SELECTEDVALUE('Table'[YearMonth]) <=_max
            ,
            1,0
        ),1
    )
)

Finally you may put 'Visual control' measure in the visual level filter and use 'YearMonth' column from 'Test' to filter the result.

Best Regards

Allan

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

[Anonymous]

Hi Allan,

This is exactly what I needed. Thank you so much for your help. 

Regards,

Sunaina Khera