Forum Discussion

Cookistador's avatar
Cookistador
Super User
1 year ago
Solved

Dynamic Hierachy depending on date range ?

Hello Everyone,

 

I'm finalizing a report and this what I would like to achieve

I have many indicator, and for some of them my timeperiod is pretty big (more thant 20 years) and for some it is pretty short (less than one year)
I got one point every month

So what I would like to acheive, is pretty simple:

If my date range is higher than 3 years, I would like to display the year on the x-axis else display it monthly
I created a field parameter

Parameter = {
    ("Calendar Day", NAMEOF('Table'[Date]), 0),
    ("Year", NAMEOF('Table'[Date].[Year]), 1)
}
 
So if I put this parameter in my chart, I can play with the axis, but now, I would like to get something more dynamics,
So I tried to create a measure like:
 
DynamicAxis =
Var MinDate = MIN(Table[Year])
Var MaxDate = MAX(Table[Year])
var DateDif = DATEDIFF(MinDate,MaxDate,YEAR)
RETURN
IF(DateDif < 2, NAMEOF('Table'[Year]),NAMEOF('Table'[Year].[Year])) but as it is returning a measure, I'm not able to use it my x-axis
Do you know a simple way to achieve that?
 
Many thanks in advance for your help 🙂 
Need Help

2 Replies

  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    1 year ago

    Cookistador 

    In Power BI, dynamic axes on visuals require a workaround since measures cannot directly be used as axis fields.

    Create a Calculated Column for the Axis Values

    Dynamic Axis =
    VAR MinDate = MIN('Table'[Date])
    VAR MaxDate = MAX('Table'[Date])
    VAR DateRangeYears = DATEDIFF(MinDate, MaxDate, YEAR)
    RETURN
    IF(
    DateRangeYears < 3,
    FORMAT('Table'[Date], "MMM yyyy"),
    FORMAT('Table'[Date], "yyyy") 
    )

    To ensure the axis sorts correctly, create an additional column for sorting:

    Axis Sort =
    IF(
    DATEDIFF(MIN('Table'[Date]), MAX('Table'[Date]), YEAR) < 3,
    YEAR('Table'[Date]) * 100 + MONTH('Table'[Date]), 
    YEAR('Table'[Date])
    )

    Sort the Dynamic Axis column by this column.

     

    💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
    Cheers,
    Kedar
    Connect on LinkedIn