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:
Hi Greg_Deckler!
I guess I am being intentionally vague. Helping my wife, so the data isn't mine and I cannot share too much of it. 😞
Maybe some images will help?
Below are the three tables in the model with the relationship I want. The relationship was created as described above: many to one, single, unique key, which was added to the calendar table with the above calculated column. Note that the calendar table is related to several other tables not shown and is also involved in several measures, though all but one of those relationships are inactive in the model.
When I filter visuals populated with measures and data from tables related to the calendar table, everything works properly. For example, as shown below, using the week number from the calendar table filters measures/visuals as expected.
Also, if I change the direction of the relationship shown above to both, then the calendar table does filter the holiday colors table, as expected. For instance, highlighting any week that corresponds to the first week of the Easter holiday reduces the legend visual to just that holiday label, as shown here:
The problem is that the holiday colors table does filter anything. As shown below, removing other filters and selecting the second week of Easter has no effect on the visual or measures. I do not understand why...
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
Greg_Deckler you are right that it is having some effect on the data. Good eye! Essentially what it is doing is it is expanding the filter context to all possible weeks in the calendar table, even ones for which there is no data for the selection. But then it replaces all values that are not within the selected holiday week with a 0, including the blanks. Hmm... so it is filtering the results in a sense, just not the visual. The measures are all set to return blanks if there is no data, so I guess I have to adjust the measures to account for this new filter context?
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.
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: