How to extend a drill through filter for a particular visual

[Anonymous]

Hi,

I'm looking for ideas for a way to exclude drill through filter in a specific visual (area chart).

Here the scenario:-

Data source: AAS model 

Summary Page

1. Table visual has few line items for each month and other dimensions.

2. From any one line item of this table, the user needs to "Drill through" from this page to another page "Country level"

3. The "country level"page catches all the filters including the month filter/attribute.

Country Page

1. It has few visuals, including an area chart.

2. The other visuals on the needs to retain the drillthrough filters including month.

3. Only the area chart visual needs to has slighted altered behaviour for the month filter (which is coming from the drill through)

4. It needs to not only plot data for the selected month, coming from the drillthorugh but also the next 6 months.

5. The data needs to be shown is these visuals (including the area chart) is coming through a measure (just sum of an amount column column)

So the problem here is: how to get more months data in the area chart visual; the month coming from the drill through filter plus next 5 months.

Is writing a new measure to use in this particular visual the right way to go about it?

If yes, what should this measure look like? I can smell potential usage of ALL DAX function.

Are there any alterate solutions or design ideas for this requirement?

Thanks for the prespectives. 

[MFelix]

Hi @Anonymous ,

The drill trough filters affect the entire page, so to my knowledge you cannot change the way the filter work for a single visualization in that even forcing the ALL in the calculation, because when you get the context filter from the whole page the axis if the area chart will be filtered.

For this what you need to do is to create another calendar table without any relationship to your fact table then you need to create 2 measures:

Sales In Dates =
CALCULATE (
    SUM ( Sales[Sales] ),
    'Calendar'[Date] IN VALUES ( 'Calendar'[Date] )
)


Filter Calendar 2 =
COUNTROWS (
    FILTER (
        'Calendar2',
        'Calendar2'[Date]
            >= DATE ( YEAR ( MAX ( 'Calendar'[Date] ) ), MONTH ( MAX ( 'Calendar'[Date] ) ), 1 )
            && 'Calendar2'[Date] <= EOMONTH ( MAX ( 'Calendar'[Date] ), 5 )
    )
)

To setup your chart you need to:

• Axis: Calendar2 Table
• Values: Sales in Dates
• Filter: Filter Calendar 2 = is not blank

This should give expected result.

If you need an example can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.