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. 

5 Replies

  • v-saisrao-msft's avatar
    v-saisrao-msft
    Icon for Community Support rankCommunity Support
    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. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Thanks, mind explaining why this works and not when using SELECTEDVALUE?

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

  • Ashish_Excel's avatar
    Ashish_Excel
    Icon for Solution Supplier rankSolution Supplier
    1 year ago

    Hi,

    Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    The above works if I have the date fields on the table visual but I do not want to have those date fields on the visual. Is there a way around?