Issues with counting distinct dates in a half hour time bucket

[otis_pc]

I'm totally stumped on this.

I have three tables in play here: DimTimes, DimDates, and Facts, where Facts is a bunch of things that occurred at a specific time, and the other two are self-explanatory.

Facts connects to DimTimes on TimeKey and to DimDates on Date.

 DimTimes

Time	HourBucket	HalfHourBucket	15MinBucket	TimeKey	Hour	Minute	IsDaytimeShift
12:00 AM	12:00AM	12:00AM	12:00AM	0	0	0	N
12:01AM	12:00AM	12:00AM	12:00AM	0	0	1	N
...
12:16AM	12:00AM	12:00AM	12:15AM	0	0	16	N
...
1:47PM	1:00PM	1:30PM	1:45PM	827	13	47	Y

Facts

Id	DateTime	Date	TimeKey	OtherColumns
1200	8/1/2021 9:49 AM	8/1/2021	589
1200	8/1/2021 5:17 PM	8/1/2021	1037
1200	8/4/2021 6:01 AM	8/4/2021	361
1200	8/4/2021 3:29 PM	8/4/2021	929
1200	8/5/2021 6:03 AM	8/4/2021	363
1200	8/5/2021 3:32 PM	8/4/2021	932

My goal is to have a bar chart with the x-axis as DimTimes[HalfHourBucket] and the y-axis as the count of dates where the minimum timestamp on that date falls into the half hour bucket (e.g., the red line).

So, for the example facts table, with half hour buckets from midnight to 11:30pm, we'd have:

HalfHourBucket	Count
6:00AM	2
9:30AM	1

and everything else 0.

I can't get the DAX to cooperate. Either it fills in the bar chart with the minimum value for each bucket, like

HalfHourBucket	Count
6:00AM	2
9:30AM	1
3:00PM	1
3:30PM	1
5:00PM	1

and everything else 0,

or it just returns a count of 1 in every half hour bucket because there is 1 minimum value (6:01AM).

If I create a table in Power Query where each row is one id/one date, with the minimum time for that date in a third column and the associated start half hour in a fourth, Power BI counts the dates as expected using this measure:

CALCULATE(
        COUNT(GroupedTable[StartHalfHour])
    )

Looking for just the minimum date (and not trying to count it yet), if the date is included in the visual, this works:

VAR vMinByDate =
    CALCULATE(
        MINX(
            DimDates
            , MIN(Facts[Time])
        )
        , REMOVEFILTERS(DimTimes)
    )

but as soon as I remove the date from the visual, this returns the minimum time across the dataset, period.

I've tried it with ADDCOLUMNS, iterator functions alone, SUMMARIZE, GROUPBY, and other combinations therein, along with moving the REMOVEFILTERS(DimTimes) to different locations in the formulas, and using either DimTimes or DimTimes[HalfHourBucket] in REMOVEFILTERS().

The following code almost works, but it randomly returns incorrect values that I can't make heads or tails out of:

dev_StartHalfHourCount = 
// count the number of dates where the half hour bucket was the minimum time

/* VAR vMinByDate =
// this works in table of date, time, value
    CALCULATE(
        MINX(
            DimDates
            , MIN(Facts[Time])
        )
        , REMOVEFILTERS(DimTimes)
    ) */

VAR x = 
// this works most of the time, but returns wonky values randomly
// doesn't work to define vMinByDate outside of the iterator

    SUMX(
        VALUES(DimDates[Date])
        , 
            VAR vMinByDateInternal =
                CALCULATE(
                    MINX(
                        VALUES(Facts[Date])
                        , MIN(Facts[Time])
                    )
                    , REMOVEFILTERS(DimTimes)
                )
            RETURN
                IF(
                    (not ISBLANK(vMinByDateInternal)) 
                        && SELECTEDVALUE(DimTimes[Half Hour Bucket]) <= vMinByDateInternal
                        && vMinByDateInternal < SELECTEDVALUE(DimTimes[Half Hour Bucket]) + TIME(0, 30, 0)
                    , 1
                    , BLANK()
                )
    )
RETURN x

For example, for a specific id, the 3:30PM value should be 56, but it returns 62. (The number of distinct dates in that time slot for that id is 136, and the number of distinct times combined with one of the other columns is 111, so I have no idea where the additional 6 is coming from.)

[Anonymous]

Hi @otis_pc ,

Please follow the steps below to get it:

1. Do not create any relationship between DimTimes and Facts table

2. Create a measure as below

Measure =
VAR _time =
    SELECTEDVALUE ( 'DimTimes'[HalfHourBucket] )
RETURN
    CALCULATE (
        COUNT ( 'Facts'[Id] ),
        FILTER (
            'Facts',
            TIMEVALUE ( 'Facts'[DateTime] ) >= _time
                && TIMEVALUE ( 'Facts'[DateTime] )
                    <= TIMEVALUE ( 'Facts'[DateTime] ) + TIME ( 0, 30, 0 )
        )
    )

3. Create a bar chart( X-axis: DimTimes[HalfHourBucket]  Y-axis: [Measure])

If the above one can't help you get the expected result, please provide some raw data in your tables (exclude sensitive data) with Text format and your expected result with special examples and screenshots. It would be helpful to get the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[otis_pc · Accepted solution]

Without creating the relationship, the suggested code returns a running total starting from the latest half hour bucket to the earliest.

I'm looking only for how many times a particular half hour bucket encompassed the earliest time stamp on any given date. So Joe Schmoe may have worked a 9a-3p shift five days in one week, but the 10:00 am bucket would be the start time 0 times in that week, even though he worked during the 10:00 am bucket all five days.

A colleague just solved it, with the relationship intact:

Create a helper column in the Facts table:

CALCULATE(

    MIN(Facts[Time])

    , FILTER(

        Facts

        , Facts[Date] = EARLIER(Facts[Date])

            && Facts[Id] = EARLIER(Facts[Id])

    )

)

And then create the actual calculated column: 

    IF(

        Facts[MinStartHelper] = Facts[Time]

        , 1

        , 0

    )

The measure then goes:

    CALCULATE(

        DISTINCTCOUNT(Facts[Date])

        , Facts[MinStart] == 1

    )

The random errors in the strictly measure attempt was from using SUMX.