Forum Discussion

AlexisGonzalez's avatar
AlexisGonzalez
Frequent Visitor
6 years ago
Solved

Calculate difference between slicer selected values based on 15 min intervals

in this case, I have selected 3 days ( march 5 - 2 and 1) on the slicer, and I would like to have a measure in a new column with the subtraction of travel time column between the max and min 15 minut...
Need Help
  • Icey's avatar
    Icey
    6 years ago

    Hi AlexisGonzalez ,

     

    Please check the attached PBIX file.

     

    1. Enter Data.

    Measure Table:

     

    2. Create "Legend Table".

    Legend Table = UNION(VALUES(Data[Day]),'Measure Table')

     

    3. Create measures.

    Average of Travel Time between Selected Days = AVERAGE(Data[Travel Time (min)])
    Difference of Travel Time between Selected Days = 
VAR MaxDay =
    MAXX ( Data, Data[Day] )
VAR MinDay =
    MINX ( Data, Data[Day] )
VAR MaxDayValue =
    CALCULATE (
        MAX ( Data[Travel Time (min)] ),
        FILTER ( Data, Data[Day] = MaxDay )
    )
VAR MinDayValue =
    CALCULATE (
        MAX ( Data[Travel Time (min)] ),
        FILTER ( Data, Data[Day] = MinDay )
    )
RETURN
    MaxDayValue - MinDayValue
    Measure = 
IF (
    MAX ( 'Legend Table'[Day] ) = "Average of Travel Time between Selected Days",
    [Average of Travel Time between Selected Days],
    IF (
        MAX ( 'Legend Table'[Day] ) = "Difference of Travel Time between Selected Days",
        [Difference of Travel Time between Selected Days],
        IF (
            VALUE ( MAX ( 'Legend Table'[Day] ) ) IN VALUES ( Data[Day] ),
            CALCULATE (
                AVERAGE ( Data[Travel Time (min)] ),
                FILTER ( Data, Data[Day] = VALUE ( MAX ( 'Legend Table'[Day] ) ) )
            )
        )
    )
)

     

    4. Create visuals.

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Icey's avatar
Icey
Icon for Community Support rankCommunity Support
6 years ago

Hi AlexisGonzalez ,

 

Please check if this meets your initial requirements:

 

1. Create a measure 'Selection'.

Selection = 
VAR Top1N =
    CALCULATE (
        TOPN ( 1, VALUES ( 'Table'[Date] ), [Date], ASC ),
        ALLSELECTED ( 'Table' )
    )
VAR Top2N =
    CALCULATE (
        TOPN ( 1, VALUES ( 'Table'[Date] ), 'Table'[Date], DESC ),
        ALLSELECTED ( 'Table' )
    )
VAR FirstSelectedValue =
    CALCULATE (
        SUM ( 'Table'[Travel Time (min)] ),
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[15 min INT] ), 'Table'[Date] = Top1N )
    )
VAR SecondSelectedValue =
    CALCULATE (
        SUM ( 'Table'[Travel Time (min)] ),
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[15 min INT] ), 'Table'[Date] = Top2N )
    )
RETURN
    SecondSelectedValue - FirstSelectedValue

 

2. Create a measure 'Is Selected'. Then put it on "Filters on this visual" of which visual needed.

Is Selected = IF ( MAX ( 'Table'[Date] ) IN VALUES ( 'Table'[Date] ), 1 )

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

AlexisGonzalez's avatar
AlexisGonzalez
Frequent Visitor
6 years ago

hello, thanks for the answers have been very helpful, I think that I now understand a little more about how DAX works.

I was checking the file and everything works as I want, but even when the days are selected using the slicer the difference between the days is not every 15 min for the whole day, the table would give me the difference every 15 min of the selected days (96 intervals per day from 12:00 am to 11:45 pm) please check the PBix file that I will upload ("Demo. pbix" link below) on the page: "my Goal" appears a table that i created for the example where average travel time appears on day 3 and day 1 and the average travel time between them (the days that are selected in the slicer) every 15 min interval by day.

the goal is to create a line graph like the one you can see below in the page where you can plot the travel time of the days selected with the slicer, and also the average travel time (or the difference in average travel time) between the first and last days selected, but to do this the data must be by every day delected and  15 minutes interval (Axis)

https://mega.nz/folder/mDB1DKCJ#BBNX6Fx2x-GZHRz4GleZKw 

thanks ...