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 selec...
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

     

     

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