DAX: Test if value in one table is between minimum and maximum from another table

[Anonymous]

Scenario: the user selects a particular month. When this happens, a month date dimension now has the minimum and maximum dates for that month.

I need to created a calculated column on another table's date column returning true or false if that date falls in that month when filtered.

The logic is fairly simple, but my issue is the filter context. For example, this works before any filtering happens:

IsCreatedDuringMonth = AND(IF(Tickets[DateCreated] > MINA(Months[MonthStart]), TRUE(), FALSE()), IF(Tickets[DateCreated] < MAXA(Months[MonthEnd]), TRUE(), FALSE()))

The issue is that MINA(MonthStart) and MAXA(MonthEnd) should change if the user selects another month, but this doesn't happen as the DAX I've written seems to ignore the report's filter context.

How can I modify this DAX so that filter context affects the calculations of MINA(Months[MonthStart]) and MAXA(Months[MonthEnd])?

EDIT:

Here's an example of my Months date dimension and the slicer. The idea is that when the user selects the "2017 January" chiclet, as far as the DAX query knows, the earliest date in that table is '2017-01-01 00:00' and the latest date is '2017-01-31 11:59'

 Month Slicer

[v-caliao-msft]

Hi @Anonymous,

According to your description, you need to create a dynamic calculated column based on the selected item in your slicer, right?

Based on my research, this requirement cannot be achieved in current version of Power BI. The calculated columns were calculated at the report initial rendering time. And it won't be re-calculated when you selected a new item in your slicer. This is why DAX seems to ignore the report's filter context.

Regards,

Charlie Liao

[Anonymous]

@v-caliao-msft, yep, that's exactly what I was trying to do. It seems odd that the Measures show the slicer's filter context, but the columns do not. Although, I suppose there are performance considerations with that kind of dynamic mass-update in the underlying data model.

I've attained some of the behavior I was looking for by creating various date dimensions and separating metrics into what relies on when a ticket is created, and what relies on when a ticket is closed, but I'm a bit stuck on one particular issue: what about metrics that need a single filter to show whether the ticket was created or closed in a particular month? What about created AND closed in a particular month?

I need to, with a single month date slicer, filter rows with two date values occuring within said month. In this case, I have ticket with a created date, and a closed date. I'd like to subtract those for a given month to get the ticket "debt" or "surplus", so to speak.

So if the user selects "October 2016", a Measure shows something like

COUNTROWS(FILTER(ALL(Tickets), Tickets[DateCreated] > MIN(ClosedMonths[MonthStart])))

which is all well and good, but unfortunately ignores any additional filters such as the client's name.

So I suppose the crux of the issue is that I know how to ignore filter context with ALL(), but really what I need to do is ignore a single, specific filter in only part of the DAX statement, that is, I need to ignore the slicer filtering the ticket's closed date only long enough to see if it was created in that particular month.

This is probably rambling at this point, but I'm still tinkering.

[Vvelarde]

@Anonymous

hi, you can modify your measure to include the columns in your filter:

A sample:

COUNTROWS(FILTER(ALLEXCEPT(Tickets;Tickets[ClientID]), Tickets[DateCreated] > MIN(ClosedMonths[MonthStart])))

[Anonymous · Accepted solution]

Thanks for the tip, @Vvelarde, didn't know about that DAX function.

However, I've done a bit of a workaround for now using separate related date dimensions for created/closed dates on tickets.

[alanhodgson]

Hey @Anonymous,

What is the relationship between your 'Months' table and 'Tickets' table?

Best,

Alan

[Anonymous]

@alanhodgson, there isn't a relationship.

I may be looking at this the wrong way, but I don't think I should necessarily need one.