I have a large dataset with many rows. I want to be able to filter the power bi table visual as provided below based on multiple criteria using measure added to a parameter with slicer.
One filter selection is to be able to see orders not delivered AND days from order date is already greater than 3 days.
Order ID | Country | Order Date | Packed Date | Delivered Date | Days from Order |
---|---|---|---|---|---|
A1111 | SG | 2024-11-01 | 2024-11-03 | 2024-11-04 | 7 |
B2222 | MY | 2024-11-02 | 2024-11-04 | 2024-11-05 | 6 |
C3333 | IN | 2024-11-03 | 2024-11-05 | 5 | |
D4444 | ID | 2024-11-04 | 2024-11-06 | 2024-11-07 | 4 |
E5555 | HK | 2024-10-31 | 2024-11-02 | 8 | |
F6666 | VN | 2024-11-01 | 2024-11-03 | 2024-11-04 | 7 |
G7777 | SG | 2024-11-03 | 2024-11-05 | 2024-11-06 | 5 |
H8888 | MY | 2024-11-04 | 4 |
I manage to filter the blank rows for Delivered date with this DAX measure:
No delivered date =
CONCATENATEX (
FILTER (
'dataset',
ISBLANK('dataset'[Delivered Date])
),
'dataset'[Delivered Date]),
", "
)
If I add another criteria (days from Order Date > 3) to filter based on multiple criteria, it does not work.
CONCATENATEX (
FILTER (
'dataset',
ISBLANK('dataset'[Delivered Date]) &&
'dataset'[Order Date]) + 3 > TODAY()
),
'dataset'[Delivered Date]),
", "
)
Would appreciate any advice on how to use DAX measure to filter based on multiple criteria.
I would go for creating a calculated column to use it as a filter on your table visual with the value of 1 :
OverdueOrdersFlag =
IF(
ISBLANK('dataset'[Delivered Date]) &&
DATEDIFF('dataset'[Order Date], TODAY(), DAY) > 3,
1,
0
)
If you want to use a measure (which I don't recommend here) :
NoDeliveredAndOverdueOrders =
CONCATENATEX (
FILTER (
'dataset',
ISBLANK('dataset'[Delivered Date]) &&
DATEDIFF('dataset'[Order Date], TODAY(), DAY) > 3
),
'dataset'[Order ID],
", "
)