Allow a single visual to ignore report level filters

[andrmh]

Hello all,

Im having an issue that I keep getting confused on, and suspect im making it alot harder than it needs to be.

I have a report containing two tables dimDate, and Workorders.

Dimdate is my date dimension and workorders is service work our techicians carry out.
They are joined on date (type date - not datetime), and created date (type date). Referential integrity is enforced.

The date dimenstion has a calculation of it that returns the MAX date, of the dates selected in the report filter.

The visual (below) displays 4 plots. All but the 4th - Total WorkOrder by MonthName Label, should obey the report date filter.

This 4th visual however, i want:

* take the MaxSelectedDate

* calculate a 'start date' that is 6 months prior to the MaxSelectedDate

* Then, using all data available to both tables, while observing the relationship of the join, show the 4th visual as just the 6 months from the MaxSelectedDate - 6months to MaxSelectedDate

So far using ALL, ALLSELECTED etc is just getting me in trouble, as i still want to observe the grouping on month/year established in the relationship, while ignoring the selection context. That said, I need maxselecteddate to dynamically build my range.....

I think i needs one, possibly 2 calculated tables here, but not sure how to go about it.

Any help is appreciated.

The graphic shown above is with ALL dates selected.

Thanks,

Andrew

[v-yulgu-msft · Accepted solution]

Hi @andrmh,

You may need to duplicate the TestWorkorders table and keep the duplicated one unrelated to DimDate table.

In DimDate, add two calculated columns.

DateEnd = DimDate[Date]

DateStart = DATEADD(DimDate[Date],-6,MONTH)

And set their date format as below. Later, you should add [DateEnd] to report level filters.

Then, create new measures for the 4th chart based on the duplicated table,

Closed Work Orders2 = 
var maxDate = SELECTEDVALUE(DimDate[DateEnd])
var minDate = SELECTEDVALUE(DimDate[DateStart])

var totalWO = CALCULATE(
	CALCULATE(
	COUNTA('TestWorkOrders2'[WONumber]),
	'TestWorkOrders2'[WOState] IN { "CLOSED" }),
    filter(TestWorkOrders2, TestWorkOrders2[dateCreated]>=minDate&&TestWorkOrders2[dateCreated]<=maxDate) 
)

return
if(totalWO = BLANK(), 0, totalWO)


Completed Work Orders2 = 
var maxDate = SELECTEDVALUE(DimDate[DateEnd])
var minDate = SELECTEDVALUE(DimDate[DateStart])

var totalWO = CALCULATE(
 CALCULATE(
 COUNTA('TestWorkOrders2'[WONumber]),
 'TestWorkOrders2'[WOState] IN { "COMPLETED" }),
 filter(TestWorkOrders2, TestWorkOrders2[dateCreated]>=minDate&&TestWorkOrders2[dateCreated]<=maxDate) 
)

return
if(totalWO = BLANK(), 0, totalWO)

Total Work Order = [Closed Work Orders2] + [Completed Work Orders2]

Regards,

Yuliana Gu

[Greg_Deckler]

Wondering if GENERATE might be the answer here but won't know without sample data, etc. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[andrmh]

Thankyou Greg.

Ive shared a sample pbix.

Hopefully the link works.

https://www.dropbox.com/sh/caytzpevr91ycap/AAB9avk1EadFtMDnQ-fDTBVOa?dl=0

Additional note on the workings:

the first 3 visuals, and the title all operate on displaying data relevant to the MAX month end date selected in the report filters. ie, if you pick Dec 17, Jan 18, Feb 18, the visuals will display data for feb 18. Hence the formulas filtering on MaxSelectedDate.

for context, there will be subsequent pages that rely on the date filter.