Use single Slicer to filter rows for multiple date column

[kaushikhalvadia]

I am a beginner at Power BI and currently facing an issue while developing a report.

I have created SQL query which pulls information from multiple tables and the query returns around 10-12 columns of which two are Date and FirstOrderDate columns.

Example:

On Report, I will use a single slicer(Between types). 

When the user selects any date range from the slicer at that time I want the report to filter records based on both columns(Date and FirstOrderDate)

[BA_Pete]

Hi @kaushikhalvadia ,

I'm detecting an XY Problem here.

Can you give details around what metrics/values you want to be able to calculate in your report please? It's most likely that you just need to create the correct relationships and measures, rather than worry about how your table gets filtered.

Pete

[kaushikhalvadia]

Hi @BA_Pete 

I need this filter to be applied and I'll use COUNT to get the number of orders within that date range. The SQL query which I already have created has OrderNumber column along with Date and FirstOrderDate so I'll use COUNT to check the total number of orders.

Example.

Thanks

[BA_Pete]

Ok, I think I have an idea of what you're after.

1) Create a calendar table and mark it as a date table. There's thousand of articles of how to do this online so I'll not go into detail here. FWIW, I would recommend using an M code calendar and putting it into a Dataflow, so it's always accessible to future reports.

2) Relate your calendar table to your fact table in the model as follows:

- calendar[Date] ONE : MANY fact[Date] (ACTIVE)

- calendar[Date] ONE : MANY fact[FirstOrderDate] (INACTIVE)

3) Create your measures:

// Count of orders
_noofOrders =
DISTINCTCOUNT(fact[Number])

// Count of first orders
_noofFirstOrders =
CALCULATE(
    DISTINCTCOUNT(fact[Number])
    USERELATIONSHIP(calendar[Date], fact[FirstOrderDate])
)

 4) Use calendar[Date] in your slicer and any visuals where you want to show values vs date.

Pete

[kaushikhalvadia]

Hi @BA_Pete 

Thanks for your reply.

I have implemented it as per your suggestion but looks like something I have missed or done wrong. Could you please see the attached file and let me know what is wrong here?

It doesn't filter records as per requirement.

If I provide 8/6/2018 as both StartDate and EndDate, it displays records with FirstOrderDate in 2010, 2015 and even 1995 so something is wrong here. 

[BA_Pete]

Hi @kaushikhalvadia ,

Okay, I see. I assumed you wanted to show metrics around these dates rather than just filter the table based on an OR condition. No matter, we've done half the work we need to do anyway.

1) In your modelling screen, double-click the active relationship and, in the dialog, set it to inactive.

2) Update your [_noofOrders] measure to this:

// Now seeing your model, this may actually be called _noofNewCustomers or similar
_noofOrders =
CALCULATE(
    DISTINCTCOUNT(NewCustomer[Number])
    USERELATIONSHIP(CalendarTable[Date], NewCustomer[Date])
)

3) Create a new filter measure:

_visualFilter = IF([_noofOrders] > 0 || [_noofFirstOrders] > 0, 1)

4) place your [_visualFilter] measure into the visual filter area of your table, then set it to 'is 1'.

This should now work as required.

Pete

[kaushikhalvadia]

Hi @BA_Pete 

I tried but not getting the expected output. See below.

[BA_Pete]

It looks like it's all set up correctly, but can't see the output or why it's not as expected.

Can you give me a bit more info about why this isn't working how you need it to please? It worked fine in my own tests.

If you can provide a small bit of anonymised example data from your NewCustomer table (just [ID], [Date], [FirstOrderDate]) I can knock you up a working sample file.

Pete

[kaushikhalvadia]

Hi @BA_Pete 

The same question I have asked in a different way and with SQL query. Could you please see this?