cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ReubenLam
Frequent Visitor

Using a filter as a value in an expression.

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

3 REPLIES 3
dkay84_PowerBI
Microsoft
Microsoft

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/

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.

 

 

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?

Helpful resources

Announcements
Exciting changes

Power BI Community Changes

Check out the changes to the Power BI Community announced at Build.

May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Kudo Data Story carousel

Data Stories Gallery

Visit our Data Stories Gallery and give kudos to your favorite Data Stories.

Top Solution Authors