powerbidax

DAX filter measure based on multiple critera


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.


Solution

  • 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
    )
    

    enter image description here

    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],
        ", "
    )
    

    enter image description here