Unable to filter graph based on slicer selection

[vkedd]

Hey all! Have been working with PowerBI for a bit, but this one has me needing assistance.

I have a bar/line chart combo with Creation Date on the X axis, TCV and Deal count on the y axis. I also have a slicer at the top of the page for fiscal date (Allows for a selection of YR and Quarter). I intended to the have user select a fiscal date, which would then filter this graph to the proper creation date.

So user selects 2023 Qtr 1 & 2 fiscal date-> graph filters down to this fiscal date and shows the proper TCVs and deal counts. The issue I'm coming across is that when I use a column reference within the same table, it breaks the filtering.

This doesn't work:

create date filter =

var selectedfiltervalue = VALUES('table1'[Fiscal Period])

return IF(MAX('table1'[Create Date fiscal period]) in selectedfiltervalue, 50, 0)

 

This does work:

create date filter =

 var selectedfiltervalue = {"Q1-2023", "Q2-2023"}

return IF(MAX('table1'[Create Date fiscal period]) in selectedfiltervalue, 50, 0)

I would then add this as a visual filter and set it to only display '50' (using 50 here because it's easy to debug visually on the graph - see above graphs)

 NOTE: Our fiscal yr begins in April

Can someone help me troubleshoot why a hardcoded string works in this example, but a column reference breaks? I've tried different manners of using all() and allexcept() to no avail. I cannot make a date table as it would require too much reworking in the backend.

[RossEdwards · Accepted solution]

I feel like you are going down the wrong path in your design.  Generally in measures you just want to use the context to get to your result.  I'd approach it more like:

Graph Output = var startDate = MIN('table1'[Fiscal Period])
var endDate = MAX('table1'[Fiscal Period])
RETURN
CALCULATE(
	SUM('table1'[YourOutputValueColumn]),
	ALL('table1'),
	'table1'[Create Date fiscal Period]) >= startDate,
	'table1'[Create Date fiscal Period]) <= endDate
)