Forum Discussion

ahanfouf's avatar
ahanfouf
Frequent Visitor
3 years ago
Solved

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.

 

 

 

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

Need Help
  • Ritaf1983's avatar
    Ritaf1983
    3 years ago

    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

    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" )
            )
        )

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

    *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

     

     

3 Replies

  • Ritaf1983's avatar
    Ritaf1983
    Super User
    3 years ago

    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

    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" )
            )
        )

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

    *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

     

     

  • ahanfouf's avatar
    ahanfouf
    Frequent Visitor
    3 years ago

    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.

     

     

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

     

    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 ?! 

     

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

     

    Thank you anyways very much in advance!!! 😊 

     

     

    • Ritaf1983's avatar
      Ritaf1983
      Super User
      3 years ago

      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.

       

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