Forum Discussion

PC20's avatar
PC20
Frequent Visitor
3 years ago

Dynamically Change field axis in line chart

I have a line chart to display trend over a period of time. It should display data based on week, month, quarter based on a slicer selection.  It should also restrict the data displayed on chart. F...
Need Help
v-jingzhang's avatar
v-jingzhang
Community Support
3 years ago

Hi PC20 

 

You may try the solution introduced in this blog Dynamic X axis on charts - Power BI - RADACAD. Limit the date ranges in the Dynamic Measure that is on the y-axis. For example, 

Dynamic Measure =
VAR SlicerID = MAX ( 'Slicer Table'[ID] )
RETURN
    SWITCH (
        TRUE (),
        SlicerID = 1,
            IF (
                MAX ( 'Data Table'[Month] )
                    >= EDATE ( TODAY () - DAY ( TODAY () ) + 1, -11 )
                    && MAX ( 'Data Table'[Month] )
                        <= TODAY () - DAY ( TODAY () ) + 1,
                CALCULATE (
                    [Sum of My Value],
                    USERELATIONSHIP ( 'Data Table'[Month], Dates[Date] )
                ),
                BLANK ()
            ),
        SlicerID = 2,
            IF (
                MAX ( 'Data Table'[Quarter] )
                    >= EDATE (
                        DATE ( YEAR ( TODAY () ), ( QUARTER ( TODAY () ) - 1 ) * 3 + 1, 1 ),
                        -9
                    )
                    && MAX ( 'Data Table'[Quarter] )
                        <= DATE ( YEAR ( TODAY () ), ( QUARTER ( TODAY () ) - 1 ) * 3 + 1, 1 ),
                CALCULATE (
                    [Sum of My Value],
                    USERELATIONSHIP ( 'Data Table'[Quarter], Dates[Date] )
                ),
                BLANK ()
            ),
        BLANK ()
    )

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.