Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Dunner2020
Post Prodigy
Post Prodigy

using measure within measure gives different result

Hi there,

 

I have data set which looks like as follows:

 

Start TimeHalf Hour NumberTotalMinutes
2/04/2020 23:56210825530053
3/04/2020 2:372108261256
6/04/2020 1:28210840252614
6/04/2020 7:0321084141173
7/04/2020 9:132108466232
7/04/2020 9:302108467315
7/04/2020 13:182108474174
7/04/2020 17:33210848315075
9/04/2020 17:2121085787488
9/04/2020 19:08210858212318
14/04/2020 10:592108805290
14/04/2020 13:56210881119660
14/04/2020 14:22210881280404
15/04/2020 10:522108853172
16/04/2020 19:172108918184
20/04/2020 23:5421091191930
21/04/2020 11:36210914310166
21/04/2020 13:472109147792
23/04/2020 23:2721092621376
29/04/2020 15:01210953414724
29/04/2020 16:38210953713338

 

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 NumberTotalMinutesHalf hour valuemeasure_1 24 hoursmeasure_2 24 hours
2/04/2020 23:562108255300530.32820.32820.3282
3/04/2020 2:3721082612560.00280.3311.3155
6/04/2020 1:282108402526140.57450.57451.1491
6/04/2020 7:03210841411730.01280.58731.1619
7/04/2020 9:1321084662320.00250.00250.0025
7/04/2020 9:3021084673150.00340.0060.006
7/04/2020 13:1821084741740.00190.00790.0079
7/04/2020 17:332108483150750.16460.17250.1725
9/04/2020 17:21210857874880.08180.08180.0818
9/04/2020 19:082108582123180.13450.21630.3508
14/04/2020 10:5921088052900.00320.00320.0032
14/04/2020 13:562108811196600.21470.21780.2178
14/04/2020 14:222108812804040.8781.09583.7298
15/04/2020 10:5221088531720.00191.09463.7285
16/04/2020 19:1721089181840.0020.0020.002
20/04/2020 23:54210911919300.02110.02110.0211
21/04/2020 11:362109143101660.1110.13210.1321
21/04/2020 13:4721091477920.00860.14070.1407
23/04/2020 23:27210926213760.0150.0150.015
29/04/2020 15:012109534147240.16080.16080.3216
29/04/2020 16:382109537133380.14560.30640.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?

1 ACCEPTED SOLUTION
vivran22
Community Champion
Community Champion

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:

image.png

 

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

View solution in original post

3 REPLIES 3
vivran22
Community Champion
Community Champion

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:

image.png

 

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
Super User
Super User

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)

@HotChilli error from the first measure rectified.

 

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.