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,ShipmentStart,ShipmentEnd,DeliveryStart,DeliveryEnd. What I want is to show only those records with Order, Customer, Country information in a table visual where all of these dates falls within a chosen date. The chosen date should be from a Slicer which I have it built from the Date dimension table. 

 

I tried this approach of creating a measure and placing this on visual level filters but doesnt give me all records in the output. How can this situation be handled efficiently.

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 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 && delivery_from <= sDate && delivery_to >= sDate, 1,0)
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.