Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Filtering results on X Axis

Hi,

 

New to PowerBI so apologies if this should be obvious.

 

If I have the following data:

Fiscal YQ table

 

YQrownum
2020 Q 4        1
2020 Q 3        2
2020 Q 2        3
2020 Q 1        4
2019 Q 4        5
2019 Q 3        6
2019 Q 2        7
2019 Q 1        8

 

Then another table

YQRepNet Amount USD
2020 Q 4John Smith200
2020 Q 3Jane Doe500
2019 Q 4John Smith400

 

And I have a slicer on the YQ item, how would I ensure that:

A stacked bar chart only shows x numbers of items on the x axis.  Specifically, I wanted to show a max of last 4 quarters in the past if nothing is filtered (I have multiple years of data, the chart gets crowded, but I don't want to filter this information out as people may want to review items 3 years in the past).

 

I had created a measure:

QuarterSelectBounds = ((CALCULATE(MIN('AnalysisFiscalYearQuarterSort'[rownum]),ALLSELECTED(AnalysisFiscalYearQuarterSort[rownum]))+3))
 
and then another measure
Net Amount USD QuarterSelectBounded = IF(MIN('AnalysisFiscalYearQuarterSort'[rownum]) <= [QuarterSelectBounds], sum('Bookings'[Net Amount USD]), BLANK())
 
This works great, but only if I am using rownum on the x axis.  As soon as I swap it out to the YQ value, the min is applied on the line level.  I have done a lot of reading and feel like I might need to create some sort of disconnected table, but I'm a little lost at this point.  Any help?
 
Thanks
Need Help
  • AllisonKennedy's avatar
    AllisonKennedy
    5 years ago
    If you want this to work for all YQ then you need to use YQ in the ALLSELECTED:

    QuarterSelectBounds = ((CALCULATE(MIN('AnalysisFiscalYearQuarterSort'[rownum]),ALLSELECTED(AnalysisFiscalYearQuarterSort[YQ]))+3))

2 Replies

  • AllisonKennedy's avatar
    AllisonKennedy
    Icon for Community Champion rankCommunity Champion
    5 years ago
    If you want this to work for all YQ then you need to use YQ in the ALLSELECTED:

    QuarterSelectBounds = ((CALCULATE(MIN('AnalysisFiscalYearQuarterSort'[rownum]),ALLSELECTED(AnalysisFiscalYearQuarterSort[YQ]))+3))
  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , Create a common qtr year table, join with both table . Create a Qtr year rank in that on year qtr

     

    new column
    Qtr Rank = RANKX(all('Date'),'Date'[Qtr Start date],,ASC,Dense)

     

    measure
    This Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])))
    Last Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1))

     

    last 4 Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'), 'Date'[Qtr Rank]>=max('Date'[Qtr Rank])-4 && 'Date'[Qtr Rank]<=max('Date'[Qtr Rank])))