Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ahanfouf
Frequent Visitor

Dynamically change dimension based on Slicer value

Hello Power BI-Folks,

 

currently I am trying to do the following:

 

I have a report with a card visual. There is a tooltipp on the card that shows the average order value over time. I have selected Year-Month as the standard X-Axis of the tooltip visual. 

On the right hand side I have a year slicer. Now my problem is:

 

I want the dimension in the tooltip to be dynamically. When year slicer value is "All" I want the dimension on the X-Axis to be Year.

And when I choose a specific year, I want the dimension on the X-Axis to be Month.

 

ahanfouf_2-1687029669237.png

 

ahanfouf_1-1687029616716.png

 

I tried it with field parameters, but this doesn't seem to work... Do you guys have any idea? 🙂 

1 ACCEPTED SOLUTION
Ritaf1983
Super User
Super User

Hi @ahanfouf 

A type of automatic selection can be achieved with field parameters and another small manipulation with Dax.

Please follow these steps:

1. create  field parameters for months /years

Ritaf1983_0-1687054161472.png

2. create a measure for "automation"

Slicer_sel_falg =
VAR Numyears =
    DISTINCTCOUNT ( 'Data Table'[Year] )
VAR CurrentDateField =
    MIN ( 'Date axis'[Date axis] )
RETURN
    INT (
        OR (
            AND ( Numyears = 1, CurrentDateField = "Start of Month" ),
            AND ( Numyears > 1, CurrentDateField = "Year" )
        )
    )
Ritaf1983_1-1687054423102.png

3. filter put field parameter as axis X and filter by this flag :

Ritaf1983_2-1687054570454.png

*Works perfectly as a tooltip too.

To follow my steps, here's a link to my file with an example:Link 

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

 

 

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

View solution in original post

3 REPLIES 3
ahanfouf
Frequent Visitor

Thanks for this quick and very helpful answer @Ritaf1983 ! 🙂 It really works like a charm. 

 

There is only 1 thing I am struggling with:

I need this dynamic value also in my sparkline visual.

ahanfouf_4-1687187794411.png

 

 

Is this also possible ? I tried to replace the Monat column with the Field Parameter, but I couldn't make it work...

ahanfouf_2-1687187501190.png

 

I thought that I just need a DAX Measure exactly the same as the Top1 visual filter, but I couldn't extract the Field Parameter inside a measure ?! 

ahanfouf_3-1687187671401.png

 

Do you have any idea ? This is the link where I copied the Sparkline DAX Code: Link

 

Thank you anyways very much in advance!!! 😊 

 

 

Hi @ahanfouf 
I don't think it is possible with sparkline,
as an alternative combine cards with the regular line chart .
I have these combinations in my dashboards and it looks great.

Ritaf1983_1-1687188971242.png

 

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



Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile
Ritaf1983
Super User
Super User

Hi @ahanfouf 

A type of automatic selection can be achieved with field parameters and another small manipulation with Dax.

Please follow these steps:

1. create  field parameters for months /years

Ritaf1983_0-1687054161472.png

2. create a measure for "automation"

Slicer_sel_falg =
VAR Numyears =
    DISTINCTCOUNT ( 'Data Table'[Year] )
VAR CurrentDateField =
    MIN ( 'Date axis'[Date axis] )
RETURN
    INT (
        OR (
            AND ( Numyears = 1, CurrentDateField = "Start of Month" ),
            AND ( Numyears > 1, CurrentDateField = "Year" )
        )
    )
Ritaf1983_1-1687054423102.png

3. filter put field parameter as axis X and filter by this flag :

Ritaf1983_2-1687054570454.png

*Works perfectly as a tooltip too.

To follow my steps, here's a link to my file with an example:Link 

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

 

 

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.