Forum Discussion

mike_viz_lord's avatar
mike_viz_lord
Frequent Visitor
3 years ago

Apply Date Range Filter Based on Condition

I'm trying to add a date range filter to my report page based on Created Date. However, the Created Date filter should only apply to Record Type = "B" and return all records where Record Type = "A". So if I selected a Created Date range of 5/1/2023 - 5/3/2023 it would display Record ID's 1, 2, 4, and 5. Any help to implement this would be greatly appreciated!

 

Record IDCreated DateRecord Type
13/1/2023A
23/1/2023A
34/5/2023B
45/3/2023B
55/3/2023B

2 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    hi mike_viz_lord 

    you may use this code for further calculations:

    VAR _table =
UNION(
    CALCULATETABLE(
        VALUES(data[Record ID]),
        ALL(data[Created Date]),
        data[Record Type]="A"    ),
    CALCULATETABLE(
        VALUES(data[Record ID]),
        data[Record Type]="B"
    )
)

     

    for example, tried to verify like:

    Measure = 
VAR _table =
UNION(
    CALCULATETABLE(
        VALUES(data[Record ID]),
        ALL(data[Created Date]),
        data[Record Type]="A"    ),
    CALCULATETABLE(
        VALUES(data[Record ID]),
        data[Record Type]="B"
    )
)
RETURN
CONCATENATEX(_table, data[Record ID], ", ")

    it worked like:

     

  • mike_viz_lord's avatar
    mike_viz_lord
    Frequent Visitor
    3 years ago

    Thank you for the reply. Your solution makes sense but I'd like to display a table that contains all columns, not just a card with the measure.