Using a filter as a value in an expression.

[ReubenLam]

Here’s a problem I hope there’s an answer for.

My data table has a list of stores and in that table it includes two date fields (Store Close date and a date when the store becomes a comparative store)

I have a separate date table that I want to use as a filter. They two tables are not currently related/linked

The business requirement is that the users want to be able to set a date in the filter and see all comp stores as of that date filter. They basically want to pick a date and see what stores were comp stores AT THAT TIME.   I thought I could do this by comapring the filter date value to the column values in the table.  Saddly, No.

The rules are:

compdate >= Filter date <= ClosedDate                (Store was a comp store but wasn’t closed at the filter date time frame)

OR

CompDate >= filter date AND ClosedDate is NULL        (store is a comp store and is still open))

How can I use the filter as a value in an expression?

Basically some sort of Calculate(CountA([xxx]), date[date]>= table[CompDate], date[date]<= table[ClosedDate])

Thanks

[technolog · Accepted solution]

The problem you're facing is quite common when trying to incorporate a disconnected date table to filter another table based on different date-related criteria. In DAX (the formula language for Power BI and other Microsoft BI tools), you can indeed retrieve a single value from a filter context and use that in a calculation. In your case, you want to get the selected date from the date table's filter context.

To get the selected date from the filter context, we'll use the MAX function (or MIN, either one will work assuming only one date is selected at a time).

Let's assume your date table is named DateTable and the date column in that table is DateValue.

To get the selected date:

SelectedDate = MAX(DateTable[DateValue])
Next, let's create a measure that counts the stores that were comp stores at the selected date:

CountOfCompStores =
VAR CurrentDate = [SelectedDate]
RETURN
CALCULATE(
COUNTA('Stores'[StoreName]),
FILTER(
'Stores',
('Stores'[compdate] <= CurrentDate && CurrentDate <= 'Stores'[ClosedDate]) ||
('Stores'[compdate] <= CurrentDate && ISBLANK('Stores'[ClosedDate]))
)
)
This measure first captures the currently selected date into a variable called CurrentDate and then uses CALCULATE and FILTER to determine which rows of the 'Stores' table meet the comp store criteria based on CurrentDate.

By using this measure in your report, users can select a date from DateTable, and the measure will show the count of stores that were comp stores at that specific date, as per your rules.

Please note that this measure assumes that your store table's name is 'Stores' and has a column 'StoreName' to count. Adjust the table and column references accordingly.

[dkay84_PowerBI]

Take a look at the following. It sounds like it could be a solution for you (don't let the simplicity of the URL deter you):

http://www.daxpatterns.com/distinct-count/

[ReubenLam]

In this case, that solution doesn't work as there's no relation between the Calendar[Date] table and any of the dates in the store data table.  User picks an arbitrary date from the filter and I want to compare it to two separate dates fields in the store data.  basically using the filter as a value in a logic test.

[dkay84_PowerBI]

Not sure if you have solved this yet but I came across this post and it may help:

http://community.powerbi.com/t5/Desktop/Linking-2-or-more-dates-to-one-Master-Calendar-USERELATIONSH...

Also, I am not sure what you mean by "no relationship between the Calendar[Date] table" and the dates in your store data table.  Did you create a relationship?