How to SUM values (in a specific time range) gotten from a measure written with SELECTEDVALUE

[JohnnyCenaa]

Hi,

Any help or suggestion on how to resolve this is most welcome.

The measure I am trying to write is to calculate the sum of the Baseline calculated between a time range. So after the Baseline is calculated I want another measure to give the sum of the values between a specific time range. The Event Table has the time range I want, which is the IDA start time and IDA end time columns and that also changes based on the Eventdate column

The link attached has sample data with the measures I have written so far. On Page 1 the visual shows the measures I have been playing around with. IDABaseline is the name of the main measure I am working on, the IDABaselineTest and SumBetween 1PMAnd3PM measures are test measures I have been trying out.

Thank you everyone in advance!

https://drive.google.com/file/d/1q6Cha_ddpD8x5sUL6e93SyxCkcHJGoYo/view?usp=sharing 

[v-rongtiep-msft]

Hi @JohnnyCenaa ,

You can test the return value of each subformula, or replace selectvalue with a deterministic value, test it, and then inspect the code.

Best Regards
Community Support Team _ Rongtie

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

[OwenAuger]

Hi @JohnnyCenaa 

I had a look at this, and here are some ideas.

From your description, my understanding is that you want to filter the fact table (Query5) based on the Date and Time range in 'Event Table'.

• For testing, I create a simple measure Hourly Average Sum (see below)
• I then created a 'Time' dimension table (generally best practice when your fact table includes Time). 
• Then I created a measure Hourly Average Sum filtered by Event, that takes the date & IDA start/end times and creates a filter on date/time.
• This measure can be adapted as required. Its main purpose is to illustrate how to convert Date & Start/End times into appropriate filters.

PBIX attached. See the Test page.

Hourly Average Sum filtered by Event = 
VAR EventDateTime =  
    GENERATE (
        SUMMARIZE (
            'Event Table',
            'Event Table'[EventDate],
            'Event Table'[IDA Start Time],
            'Event Table'[IDA End Time]
        ),
        VAR StartTime = 'Event Table'[IDA Start Time]
        VAR EndTime = 'Event Table'[IDA End Time]
        RETURN
            CALCULATETABLE (
                VALUES ( Time[Time] ),
                Time[Time] >= StartTime,
                Time[Time] <= EndTime
            )
    )
VAR EventDateTimeFilter =
    TREATAS (
        SELECTCOLUMNS (
            EventDateTime,
            "@Date", 'Event Table'[EventDate],
            "@Time", Time[Time]
        ),
        DateTable[Date],
        Time[Time]
    )
RETURN
    CALCULATE (
        [Hourly Average Sum], -- replace with any required measure
        KEEPFILTERS ( EventDateTimeFilter )
    )

[JohnnyCenaa]

Hi,

Thanks a lot for looking into my problem!

The filter measure you wrote has definitely helped my overall project, I appreciate that and will continue using the steps you mentioned to filter large datasets with time.

The problem I still have is with the Baseline measure in the file, that is the measure I want to filter according to the times on the Event table, and then sum the filtered result.

I attempted that with the measure you gave but I still get blanks returned. 

This is the Baseline measure below 

Baseline =

var h = SELECTEDVALUE(Query5[Time])

var d = SELECTEDVALUE(Query5[Date])

var a = CALCULATETABLE(Query5,allselected(),Query5[Date]<d,Query5[Time]=h,Query5[WeekdayType]="Weekday")

var b = TOPN(20,a,[Date],DESC)

var c = TOPN(15,b,[HourlyAverage],DESC)

return IF(COUNTROWS(b)= 20, averagex(c,[HourlyAverage]), BLANK())

Due to the way it is written and how I want it calculated using the Selectedvalue function, when i try filtering its result to sum the values I need, it returns blanks.

Please could you help me look into this and offer any advise on how to best write a measure that filters the results of the Baseline measure based on the IDA start time and IDA end time on the Event table, and then sums the values.

Any help is appreciated.

Thanks a lot for your help with this.

[JohnnyCenaa]

This is the code I have written to tackle the issue i am trying to solve, but it returns the values without summing them all, please any ideas on how to resolve this.

SumBetween1And3PM =

VAR TimeRange = SELECTEDVALUE('Event Table'[IDA Start Time])

VAR BaselineValue = [Baseline]  

RETURN

SUMX(

    FILTER(

        ADDCOLUMNS(

            FILTER(Query5, Query5[Time] >= TimeRange && Query5[Time] < TimeRange + TIME(3, 0, 0)),

            "IsInRange", 1

        ),

        [IsInRange] = 1

    ),

    BaselineValue

Thanks.

[Greg_Deckler]

@JohnnyCenaa Try this:

SumBetween1And3PM =
VAR TimeRange = SELECTEDVALUE('Event Table'[IDA Start Time])
VAR BaselineValue = [Baseline]  
RETURN
SUMX(
    FILTER(
        ADDCOLUMNS(
            FILTER( ALLSELECTD( Query5 ), Query5[Time] >= TimeRange && Query5[Time] < TimeRange + TIME(3, 0, 0)),
            "IsInRange", 1
        ),
        [IsInRange] = 1
    ),
    BaselineValue

[JohnnyCenaa]

Please do you have any more suggestions on how I can fix this?

The sample data file is also available.

Thank you. I appreciate the help.

[JohnnyCenaa]

Thank you. I tried the edit you suggested, it sums more than the range required