Forum Discussion

sriramn84's avatar
sriramn84
Frequent Visitor
7 years ago
Solved

DateSelected +/- 1 day filter

All,

I have a datetable and filters setup and is all good.(Please find attached the pbix file). Now I have a date relationship between DriverLog and AutoPosition already.

 

What I am trying : When a row is selected in the DriverLog(screenshot below), We need to Display any records on the AutoPosition table, irrespective of the Loc, show all the BusIds  in the CreatedDate +/- 1 day range(As shown in the AutoPosition in the image). Important : I need the existing relationships.

 

 

I have tried DAX queries to create a table with the filtered valies, but couldnt get through.

(Not finding the Attach Option) Pbix File : https://gofile.io/?c=glt7Hi

Thanks in advance

 

 

Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    7 years ago

    Hello sriramn84 

    You can use a measure as a filter on the AutoPosition table to get what you are looking for.

    SelectedDriverLog = 
VAR StartDate = MIN (  DriverLog[Date]  ) -1
VAR EndDate = MAX ( DriverLog[Date] ) +1
VAR Result = 
CALCULATE(
    COUNTROWS( AutoPosition ),
    FILTER (
        AutoPosition,
        AutoPosition[CreatedDate] >= StartDate &&
        AutoPosition[CreatedDate] <= EndDate
    )
)

RETURN Result

    Then you apply this measure as a filter to the visual displaying the AutoPosition data and set it to 'Is Not Blank'

     

     

    If a user selects more than 1 row in your DiverLog visual it will use the date range to pick which rows to display in the AutoPosition visual.

2 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Icon for Super User rankSuper User
    7 years ago

    Hello sriramn84 

    You can use a measure as a filter on the AutoPosition table to get what you are looking for.

    SelectedDriverLog = 
VAR StartDate = MIN (  DriverLog[Date]  ) -1
VAR EndDate = MAX ( DriverLog[Date] ) +1
VAR Result = 
CALCULATE(
    COUNTROWS( AutoPosition ),
    FILTER (
        AutoPosition,
        AutoPosition[CreatedDate] >= StartDate &&
        AutoPosition[CreatedDate] <= EndDate
    )
)

RETURN Result

    Then you apply this measure as a filter to the visual displaying the AutoPosition data and set it to 'Is Not Blank'

     

     

    If a user selects more than 1 row in your DiverLog visual it will use the date range to pick which rows to display in the AutoPosition visual.

    • Iamnvt's avatar
      Iamnvt
      Icon for Continued Contributor rankContinued Contributor
      7 years ago

      hi,

       

      you can write this measure:

      Measure = 
VAR
selecteddate = SELECTEDVALUE(DimDate[Date])
RETURN
COUNTROWS(FILTER(CROSSJOIN(ALL(DimDate[Date]), ALL(Location[Location])), DimDate[Date] >= selecteddate -1 && DimDate[Date] <= selecteddate +1 && FIRSTNONBLANK(AutoPosition[CreatedDate],0) <> BLANK()))

      then put it in Autoposition table, or put it in the Filter Panel.

      Here is the PBI file:

      https://1drv.ms/u/s!Aps8poidQa5zk6pwiK9ARswHmJsjaQ