How to use slicer for faster filtering with date columns and periods

[Anonymous]

It sounds easy but after reading serveral posts I am breaking my head on this one.

The data is as follows

Order_ID	Order_date	Order_shipped
1	27-12-2020	7-1-2021
2	31-12-2020	8-1-2021
3	1-1-2021	24-3-2012
4	2-1-2021	24-3-2012
5	3-1-2021	3-1-2021
6	4-1-2021	18-2-2021
7	5-1-2021	16-2-2021
8	6-1-2021	15-1-2021
9	7-1-2021	14-3-2021
10	10-1-2021	31-1-2021
11	11-1-2021	31-1-2021
12	20-2-2021	14-3-2021
13	20-2-2021	NULL

When the value is null/empty it means it not shipped.

As you might can imagine it can be nice to know what happend in a period.

In this example the period is 1 January 2021 until 15 January 2021.

For this I created  a few graphs as explained below.

GRAPH A Open orders at the begin of period FORMULA Order shipped > 1-1-2021 OR IS NOT FILLED AND Order date < 1-1-2021

GRAPH B Open orders at the end of period FORMULA Order shipped < 15-1-2021 AND Order date <15-1-2021

GRAPH C Orders shipped in period FORMULA Order shipped 1-1-2021 <> 1-15-2021

GRAPH D New orders in period FORMULA Order date 1-1-2021 <> 1-15-2021

All working as expected. The only thing the filter needs to be defined manually at the moment for every graph.

It would be nice if the period could be selected one time and all the data in the graphs adjusts

[v-yalanwu-msft · Accepted solution]

Hi, @Anonymous ;

You could create a calendar table as a slicer date. then create four meausres for 4 GRAPHs , as follows:

1.create a calendar table.

slicer = CALENDAR(DATE(2020,12,27),DATE(2021,3,14))

2.create four flag measures:

flagA = IF((MAX([Order_shipped])>MIN('slicer'[Date])||MAX([Order_shipped])=BLANK())&&MAX([Order_date])<MIN('slicer'[Date]),1,0)

flagB = IF(MAX([Order_shipped])<MAX('slicer'[Date])&&MAX([Order_date])<MAX('slicer'[Date]),1,0)

flagC = IF(MAX([Order_shipped])<=MAX('slicer'[Date])&&MAX([Order_shipped])>=MIN('slicer'[Date]),1,0)

flagD = IF(MAX([Order_date])<=MAX('slicer'[Date])&&MAX([Order_date])>=MIN('slicer'[Date]),1,0)

3.Four measures were applied to the filter of each GRAPHs.

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hey Yalun Wu,

First of all thanks for you broad answer really helped a lot. This kind of structure seems to suit the current situation.  All so adding the file helped a lot.

Flag B contained as small error from my side Order Shipped Date should be larger then 15-1-2021 to display open orders at the end of period. Further more Order Shipped Date can be blank. So I adjusted the formula to. 

IF(MAX([Order_shipped])> MAX('Slicer'[Date]) || MAX([Order_shipped)=BLANK()
&& MAX([Order_date])<MAX('Slicer'[Date]) ,1,0)

This works well only it doesnt show orders before the minimum date. Furthermore applying this if statement is kind of slow with a large amount of 20000 + records is there anyway we can do this more efficient?

Thanks in advance.

---

[Anonymous]

---

@Anonymous wrote:

IF(MAX([Order_shipped])> MAX('Slicer'[Date]) || MAX([Order_shipped)=BLANK()
&& MAX([Order_date])<MAX('Slicer'[Date]) ,1,0)

This works well only it doesnt show orders before the minimum date. Furthermore applying this if statement is kind of slow with a large amount of 20000 + records is there anyway we can do this more efficient?

Thanks in advance.

---

---

The first issue I resolved by removing any active relationships between the date table and the order table. The efficiency I managed to improve by defining variables for max date or min date so.

VAR max_date = MAX('Date'[Date])
RETURN
IF(MAX([Order_shipped])> max_date || MAX([Order_shipped)=BLANK()
&& MAX([Order_date])<max_date ,1,0)

 This resolved all performance issues 🙂  

[v-yalanwu-msft]

Hi, @Anonymous ;

If you want to  show orders before the minimum date, you could use MIN('Slicer'[Date]) . 
and i think maybe it's better to have a parenthesis here.

Congratulations on solving the performance problem, by the way.
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks everything works now only this query keeps having performance issues due to the involving the = BLANK() parameter. Do you have any tips to work around it?
Query is as below.

Slicer Open order at begin date=
VAR min_date = MIN('Date'[Date])
VAR MaxOS =  MAX(Orders[Order_shipped])
VAR MaxOD = MAX(Orders[Order_date])
RETURN 
IF(MaxOD  < min_date, 
    IF( MaxOS = BLANK() || MaxOS > min_date ,1,0),0)

[v-yalanwu-msft]

Hi, @Anonymous ;

Perhaps this article will be helpful for you.

https://community.powerbi.com/t5/Community-Blog/Performance-Tuning-DAX-Part-1/ba-p/976275

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks managed so solve this to with the query below.

VAR min_date = MIN('Date'[Date])
VAR MaxOS =  MAX(Orders[Order_shipped])
VAR MaxOD = MAX(Orders[Order_date])
VAR Temp_table =
        ADDCOLUMNS(
            FILTER(
                'Orders',
                    Max(Orders[Order_date]) < min_date   
                )
            ,
            "Orders",
SWITCH(TRUE(),
Maxcompl > min_date , 1,
Maxaanml  < min_date && MaxOD = BLANK(),1))
RETURN
SUMX(Temp_table,[Orders])

 Thanks for the suggestions sometimes it is hard to find these articles yourself. In the end in the future power bi becomes more user friendly. This could be done by connecting slicers to filters. 

[Mohammad_Refaei]

Create relathioships to both Order_date and Order_shipped and activate them in your measures using USERELATIONSHIP

[Anonymous]

I see where youre going. But what would the measure look like then for graph A?

CALCULATE(COUNTROWS('Orders'),
USERELATIONSHIP( 'Orders'[Order_date], 'Orders'[Order_shipped]))

 
The starts would be something like this but then still needs to know that the order shipped date should be higher then the selected date in the slicer and the order date before the selected date in the slicer.