using measure within measure gives different result

[Dunner2020]

Hi there,

I have data set which looks like as follows:

Start Time	Half Hour Number	TotalMinutes
2/04/2020 23:56	2108255	30053
3/04/2020 2:37	2108261	256
6/04/2020 1:28	2108402	52614
6/04/2020 7:03	2108414	1173
7/04/2020 9:13	2108466	232
7/04/2020 9:30	2108467	315
7/04/2020 13:18	2108474	174
7/04/2020 17:33	2108483	15075
9/04/2020 17:21	2108578	7488
9/04/2020 19:08	2108582	12318
14/04/2020 10:59	2108805	290
14/04/2020 13:56	2108811	19660
14/04/2020 14:22	2108812	80404
15/04/2020 10:52	2108853	172
16/04/2020 19:17	2108918	184
20/04/2020 23:54	2109119	1930
21/04/2020 11:36	2109143	10166
21/04/2020 13:47	2109147	792
23/04/2020 23:27	2109262	1376
29/04/2020 15:01	2109534	14724
29/04/2020 16:38	2109537	13338

Half Hour Number column is the unique number to represent the half-hour window slot. 

I am calculating a new column using 'Half Hour Number' & 'TotalMinutes'. So I created a measure which looks like:

Half hour value = 

var Time30Min = MAX('Table'[Half Hour Number])

Return

CALCULATE ( SUMX(filter(ALL('Table'),'Table'[Half Hour Number] = Time30Min),'Table'[TotalMinutes])/91577)

I created another column that calculates the sum of half hour value measure in last 24 hours (i.e. last 48 half-hour). So I created the following measure:

measure_1 24 hours = 

var Time30Min = MAX('Table'[Half Hour Number])

Return

CALCULATE ( SUMX(filter(ALL('Table'),'Table'[Half Hour Number] <= Time30Min && 'Table'[Half Hour Number] >= Time30Min-47),'Table'[TotalMinutes])/91577)

I can create the measure_1 24 hours measure by using the first measure (Half hour value). So I tried to create another 24 hours measure using the first measure I created above. My measure looks like:

measure_2 24 hours = 

var Time30Min = MAX('Table'[Half Hour Number])

Return

CALCULATE ( SUMX(filter(ALL('Table'),'Table'[Half Hour Number] <= Time30Min && 'Table'[Half Hour Number] >= Time30Min-47),[Half hour value]))

When I compared measure_1 24 hours with meaure_2 24 hours both produced different results as shown in the following table:

Start Time	Half Hour Number	TotalMinutes	Half hour value	measure_1 24 hours	measure_2 24 hours
2/04/2020 23:56	2108255	30053	0.3282	0.3282	0.3282
3/04/2020 2:37	2108261	256	0.0028	0.331	1.3155
6/04/2020 1:28	2108402	52614	0.5745	0.5745	1.1491
6/04/2020 7:03	2108414	1173	0.0128	0.5873	1.1619
7/04/2020 9:13	2108466	232	0.0025	0.0025	0.0025
7/04/2020 9:30	2108467	315	0.0034	0.006	0.006
7/04/2020 13:18	2108474	174	0.0019	0.0079	0.0079
7/04/2020 17:33	2108483	15075	0.1646	0.1725	0.1725
9/04/2020 17:21	2108578	7488	0.0818	0.0818	0.0818
9/04/2020 19:08	2108582	12318	0.1345	0.2163	0.3508
14/04/2020 10:59	2108805	290	0.0032	0.0032	0.0032
14/04/2020 13:56	2108811	19660	0.2147	0.2178	0.2178
14/04/2020 14:22	2108812	80404	0.878	1.0958	3.7298
15/04/2020 10:52	2108853	172	0.0019	1.0946	3.7285
16/04/2020 19:17	2108918	184	0.002	0.002	0.002
20/04/2020 23:54	2109119	1930	0.0211	0.0211	0.0211
21/04/2020 11:36	2109143	10166	0.111	0.1321	0.1321
21/04/2020 13:47	2109147	792	0.0086	0.1407	0.1407
23/04/2020 23:27	2109262	1376	0.015	0.015	0.015
29/04/2020 15:01	2109534	14724	0.1608	0.1608	0.3216
29/04/2020 16:38	2109537	13338	0.1456	0.3064	0.4672

Apparently, measure_1 24 hours produces the correct result compared to measure_2 24 hours. Could anyone shed where am I making mistake in calculating measure_r 24 hours?

[vivran22 · Accepted solution]

Hello @Dunner2020 ,

As pointed out by @HotChilli , you do not require CALCULATE in your DAX measure. 

Half hour value = 
VAR Time30Min =
    MAX ( 'Table'[Half Hour Number] )
RETURN
    SUMX (
        FILTER ( ALL ( 'Table' ), 'Table'[Half Hour Number] = Time30Min ),
        'Table'[TotalMinutes]
    ) / 91577


measure_1 24 hours = 
VAR Time30Min =
    MAX ( 'Table'[Half Hour Number] )
RETURN
    SUMX (
            FILTER (
                ALL ( 'Table' ),
                'Table'[Half Hour Number] <= Time30Min
                    && 'Table'[Half Hour Number] >= Time30Min - 47
            ),
            'Table'[TotalMinutes]
        ) / 91577
    



measure_2 24 hours = 
VAR Time30Min =
    MAX ( 'Table'[Half Hour Number] )
RETURN
    SUMX (
            FILTER (
                ALL ( 'Table' ),
                'Table'[Half Hour Number] <= Time30Min
                    && 'Table'[Half Hour Number] >= Time30Min - 47
            ),
            [Half hour value]
        )


Difference = [measure_1 24 hours] - [measure_2 24 hours]

When I am excluding it, and then using in the visual, I am not getting any difference:

The objective and subsequent logic is not very clear from your description and formula (what is the structure of the Half Hour Code and why you are dividing it by 91577), so do share more details if the problem persists.

Cheers!
Vivek

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

[HotChilli]

Before we look at the results, can you correct the syntax please.

The first measure doesn't pass a syntax check

and the other 2 measures refer to a field [epoch half hour number] which doesn't exist.

From a quick look, it also seems like the CALCULATE is not required in each measure (please check)

[Dunner2020]

@HotChilli error from the first measure rectified.