Filter table based on date ranger slicer

[nathass]

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)

[v-saisrao-msft · Accepted solution]

Hi @nathass, 
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. 

[nathass]

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

[nathass]

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?

[Ashish_Excel]

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.

[bhanu_gautam]

@nathass 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.