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.
For eg: For Slicer=Quarter, it should only display upto past 4 quarters (including current) based on today. So for today's date it should display as: Q4 2022, Q1 2023, Q2 2023, Q3 2023.
So in chart, only these 4 quarter values should be in the axis.
For Slicer=Month, it should display upto past 12 months.

I have tried using the Field Parameter (Modelling > Parameter > Field > Selected Month,Quarter from Calendar table) and i can display the data using month/date/year,etc but I can't filter it out to display limited data. I tried building a measure on the Field parameter but Field parameter values are not being read into it.

Parameter table looks like:
Parameter = {
("Year", NAMEOF('CalendarDate'[Year]), 0),
("Month", NAMEOF('CalendarDate'[Month]), 1)
}


Measure used:

Count = SWITCH(TRUE(),
"Month" IN ALLSELECTED(Parameter[Parameter]), CALCULATE (COUNTX(<field>), <Condition>),
,0)

This throws an error: 

Calculation error: Column[Parameter] is part of composite key, but not all columns of the composite key are included in the expression or its dependent expression.



 

Need Help

3 Replies

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

  • ducbim's avatar
    ducbim
    Regular Visitor
    2 years ago

    Hi anyone know has the challenge been solved yet? A slicer which can change both granularity of x-axis (day, month, quarter) and the days limit.