Forum Discussion
Using another table (e.g., holidays) to filter a calendar table
MJEnnis From your last image, it does look like it is having an effect. Everything other than the 04/04/2015 row is all 0's so something is going on. If you want rows to not show up you could use a Complex Selector. The Complex Selector - Microsoft Fabric Community
Figured it out! Your complex selector post was helpful, but the solution was much simpler in my case. I have a measure that calculates the total number of any given product available on any given calendar day, considering the start and end season for each product. All I had to do was tell my existing measures to return a blank if the [units available] measure was blank. Works like a charm, as you can see below. Still have no clue why it replaces all other dates with 0 without this condition. But not going to rack my brain anymore... 😄 Thanks again! Just chatting with you seems to lead me to a solution everytime!
New measure (guess I will test if the first condition is redundant now, since it already passes through [units available]):Vacancy = Var OPEN_Date = MIN('Lista Tipologie delle Risorse'[InizioStagione]) Var Close_Date = MAX('Lista Tipologie delle Risorse'[FineStagione]) Var d = MAX('Calendar'[Date]) VAR vacancy_ = [Units Available] - [Occupancy] RETURN IF(d < OPEN_Date || d > Close_Date, BLANK(), IF(ISBLANK([Units Available]), BLANK(),vacancy_))
Screenshot of it working:
Greg_Deckler This is the measure that populates the visual in the image. It considers the season opening and closing dates for the product in question. It should return blanks for all dates outside the "season", and the visual normally automatically collapses to exclude those date ranges. That is the way it works with the other slicers on the page. But this new one replaces those blanks with zeros and includes them. It also replaces with zeros all values within the "season" but outside the selected holiday week. Maybe I could try to create a new measure that determines if the [date] is included in the current selection of holiday weeks or not. Then use that new measure to add a condition to the measure below? On the right track?
Vacancy =
Var OPEN_Date = MIN('Lista Tipologie delle Risorse'[InizioStagione])
Var Close_Date = MAX('Lista Tipologie delle Risorse'[FineStagione])
Var d = MAX('Calendar'[Date])
VAR vacancy_ = [Units Available] - [Occupancy]
RETURN IF(d < OPEN_Date || d > Close_Date, BLANK(), vacancy_)
MJEnnis Hmm. Something is not right then. What I would do is that since you are using VAR's (good job), I would try returning OPEN_Date and then Close_Date, and d. No IF statement or anything, just return each of those one at a time to see what their values are within the context of the table visual when you make a holiday selection in the slicer. I'm guessing that something is potentially going haywire where it is always returning vacancy_ and not BLANK because of some reason.