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
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 =
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 =
measure_2 24 hours =
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?
Solved! Go to 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
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
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)
User | Count |
---|---|
115 | |
95 | |
87 | |
76 | |
65 |
User | Count |
---|---|
138 | |
113 | |
110 | |
98 | |
93 |