Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Filter table based on date ranger slicer

I have a transaction table with several date columns and also a date dimension table defined in Power BI. For example, the transaction table contains Order, Customer, Country, OrderStart,OrderEnd,Sh...
Need Help
  • v-saisrao-msft's avatar
    v-saisrao-msft
    1 year ago

    Hi Anonymous, 
    Thank you for reaching out to the Microsoft fabric community forum. Thank you bhanu_gautam, for your input on this issue. 

    After thoroughly reviewing the details you provided, I was able to reproduce the scenario, I have used it as sample data on my end and successfully implemented it.   

    Dax Measure: 

    IsWithinSelectedRange = 
VAR SelectedStart = MIN('01. Dim_Date'[Date])
VAR SelectedEnd   = MAX('01. Dim_Date'[Date])
RETURN
IF (
    MIN(Fact_RO_Contract'[OrderStart]) >= SelectedStart &&
    MAX(Fact_RO_Contract'[OrderEnd])   <= SelectedEnd &&
    MIN(Fact_RO_Contract'[DeliveryStart]) >= SelectedStart &&
    MAX(Fact_RO_Contract'[DeliveryEnd])   <= SelectedEnd &&
    MIN(Fact_RO_Contract'[ShipmentStart]) >= SelectedStart &&
    MAX(Fact_RO_Contract'[ShipmentEnd])   <= SelectedEnd,
    1,
    0
)

    Output: 

    If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly. 
     
    Thank you. 

bhanu_gautam's avatar
bhanu_gautam
Icon for Super User rankSuper User
1 year ago

Anonymous Make sure all date columns in your transaction table and the date column in your date dimension table are of the same data type (Date).

 

Then create measure

DAX
IsValid =
VAR sDate = MAX('01. Dim_Date'[Date])
VAR order_from = SELECTEDVALUE('04. Fact_RO_Contract'[OrderStart])
VAR order_to = SELECTEDVALUE('04. Fact_RO_Contract'[OrderEnd])
VAR shipment_from = SELECTEDVALUE('04. Fact_RO_Contract'[ShipmentStart])
VAR shipment_to = SELECTEDVALUE('04. Fact_RO_Contract'[ShipmentEnd])
VAR delivery_from = SELECTEDVALUE('04. Fact_RO_Contract'[DeliveryStart])
VAR delivery_to = SELECTEDVALUE('04. Fact_RO_Contract'[DeliveryEnd])
RETURN IF(
order_from <= sDate && order_to >= sDate &&
shipment_from <= sDate && shipment_to >= sDate &&
delivery_from <= sDate && delivery_to >= sDate,
1,
0
)

 

Use this measure as a visual-level filter in your table visual. Set the filter to show records where IsValid equals 1.

Ensure that there is a proper relationship between your transaction table and the date dimension table. This relationship is crucial for the slicer to filter the data correctly.