cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

Dynamic Selection of Slicer for Character Values

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Dynamic Selection of Slicer for Character Values

Hi, @Anonymous 

 

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

Table:

c1.png

 

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.

c2.png

c3.png

c4.png

 

Best Regards

Allan

 

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

 

View solution in original post

2 REPLIES 2
Highlighted
Community Support
Community Support

Re: Dynamic Selection of Slicer for Character Values

Hi, @Anonymous 

 

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

Table:

c1.png

 

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.

c2.png

c3.png

c4.png

 

Best Regards

Allan

 

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

 

View solution in original post

Highlighted
Anonymous
Not applicable

Re: Dynamic Selection of Slicer for Character Values

Hi Allan,

 

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

 

Regards,

Sunaina Khera

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors