conditional count

[Anonymous]

Hello all,

I'm newbie to PowerBI and I need the comunity help. 

I have a table with 3 columns. The first column is "days" of July, the second column, the "hour" of a day when the temprature is recorderd and the third column "temperature" for each hour of a day.
I would like to count the number of rows on the"hour" column till the highest "tempreature" in reached for each day.

In other words I have to find out :

1. The maximum tempreature in each day.
2. At what time (hour) the temprature reaches its maximum, (count the number of rows on the hours column till the maximum temperature).
3. Since the temprature was not recorded for all the hours of each day, I need to know the total number of datapoints (the number of records) for each day. 

Please find below the data.

Thanks

Q007

Day	Hour	Temperature
17	0	15.49
17	1	14.72
17	2	14.04
17	8	14.76
17	9	16.91
17	10	18.58
17	11	20.18
17	12	21.57
17	13	22.8
17	14	23.84
17	15	24.58
17	16	24.99
17	17	24.24
17	18	23.92
17	19	23.48
17	20	22.94
17	21	21.44
17	22	19.91
17	23	19.03
18	0	18.53
18	1	16.39
18	2	14.45
18	3	12.73
18	10	19.8
18	11	21.21
18	12	20.49
18	13	19.68
18	14	19.94
18	15	20.95
18	16	18.63
18	17	17.71
18	18	16.58
18	19	15.39
18	20	14.56
18	21	13.49
18	22	12.59
18	23	12.69
19	6	11.28
19	7	12.17
19	8	13.08
19	9	14.41
19	10	15.59
19	11	15.73
19	12	15.5
19	13	14.8
19	14	14.4
19	15	13.51
19	16	12.78
19	17	12.36
19	18	12.29
19	19	12.65
19	20	12.82
19	21	12.41
19	22	11.87
19	23	11.81
20	0	11.92
20	1	12.11
20	2	12.37
20	3	12.55
20	4	12.17
20	5	11.17
20	6	11.99
20	7	12.71
20	8	13.94
20	9	16.19
20	10	18.24
20	16	21.3
20	17	21.95
20	18	21.13
20	19	21.1
20	20	20.56
20	21	19.26
20	22	18.71
20	23	17.93
21	0	17.22
21	1	17.01
21	2	16.28
21	3	15.46
21	4	14.7
21	5	14.09
21	6	13.93
21	7	15.04
21	8	17.26
21	9	19.16
21	10	20.43
21	11	21.64
21	12	22.65
21	13	23.57
21	14	24.32
21	15	24.87
21	21	20.6
21	22	17.92
21	23	16.34
22	0	15.14
22	1	14.19
22	2	13.52
22	3	12.81
22	4	11.96
22	5	11.03
22	6	10.78
22	7	14.29
22	10	22.53
22	11	24.5
22	12	26.14
22	13	27.48
22	14	28.41
22	15	29.13
22	16	29.61
22	17	30.02
22	18	30.1
22	19	29.48
22	20	28.14
22	21	24.45
22	22	22.01
22	23	20.94
23	0	19.69
23	1	18.71
23	2	17.89
23	3	16.57
23	4	15.58
23	5	14.8
23	6	14.43
23	7	17.86
23	8	22.07
23	9	25.09
23	10	27.29
23	14	33.04
23	15	33.76
23	16	34.15
23	17	34.34
23	18	34.26
23	19	33.59
23	20	32.11
23	21	29.8
23	22	27.79
23	23	25.78
24	0	24.17
24	1	22.37
24	2	21.16
24	10	23.57
24	11	27.68
24	12	29.26
24	13	29.15
24	14	29.82
24	15	29.85
24	16	29.44
24	17	28.47
24	18	26.85
24	19	24.72
24	20	21.18
24	21	17.77
24	22	16.79
24	23	16.2

[Zubair_Muhammad]

@Anonymous 

What is your expected output with this sample data?

[Anonymous]

Hello,

My expection is to know:

• The max temperature in each given day.
• How many datapoints have been counted to get to the max temperature in each day.
• How many datapoints we have for each day.

Best

Q007

[Zubair_Muhammad · Accepted solution]

@Anonymous 

Try these MEASURES

File attached as well with your sample data

Max_Temp = Max(Table1[Temperature])

DataPoints_Till_MaxTemp =
VAR maxtemp = [Max_Temp]
VAR MyHour =
    CALCULATE ( MIN ( Table1[Hour] ), Table1[Temperature] = maxtemp )
RETURN
    COUNTROWS ( FILTER ( Table1, Table1[Hour] <= MyHour ) )

Total Data Points = Count(Table1[Temperature])

[Anonymous]

Sure,

I have posted another question. Did you see it? This time I have two extra columns, country and city.

I would like to do the same thing as we did before but this time spit by town and country.

Best

Q

[Anonymous]

Thanks a lot
Now I have a table with 5 columns. The first column is "Country", the second column is "Town", third is "Days" of July, the fourth column, the "Hour" of a day when the temperature is recorded and the fifth column "temperature" for each hour of a day.
I would like to count the number of rows on the “hour" column till the highest "temperature" in reached for each day in any city and country.
My expectations are to know:
1. The max temperature in each given day, by town by country.
2. How many data points have been counted to get to the max temperature in each day?
3. How many data points we have for each day?
BestQ007

Country	Town	Day	Hour	Temperature
Germany	Frankfurt	17	0	15.49
Germany	Frankfurt	17	1	14.72
Germany	Frankfurt	17	2	14.04
Germany	Frankfurt	17	8	14.76
Germany	Frankfurt	17	9	16.91
Germany	Frankfurt	17	10	18.58
Germany	Frankfurt	17	11	20.18
Germany	Frankfurt	17	12	21.57
Germany	Frankfurt	17	13	22.8
Germany	Frankfurt	17	14	23.84
Germany	Frankfurt	17	15	24.58
Germany	Frankfurt	18	16	18.63
Germany	Frankfurt	18	17	17.71
Germany	Frankfurt	18	18	16.58
Germany	Frankfurt	18	19	15.39
Germany	Frankfurt	18	20	14.56
Germany	Frankfurt	18	21	13.49
Germany	Frankfurt	18	22	12.59
Germany	Frankfurt	18	23	12.69
Germany	Frankfurt	19	6	11.28
Germany	Frankfurt	19	7	12.17
Germany	Frankfurt	19	8	13.08
Germany	Frankfurt	19	9	14.41
Germany	Frankfurt	19	10	15.59
Germany	Frankfurt	19	11	15.73
Germany	Frankfurt	19	12	15.5
Germany	Frankfurt	19	13	14.8
Germany	Frankfurt	19	14	14.4
Germany	Frankfurt	19	15	13.51
Germany	Frankfurt	20	5	11.17
Germany	Frankfurt	20	6	11.99
Germany	Frankfurt	20	7	12.71
Germany	Frankfurt	20	8	13.94
Germany	Frankfurt	20	9	16.19
Germany	Frankfurt	20	10	18.24
Germany	Frankfurt	20	16	21.3
Germany	Frankfurt	20	17	21.95
Germany	Frankfurt	20	18	21.13
Germany	Frankfurt	20	19	21.1
Germany	Frankfurt	20	20	20.56
Germany	Frankfurt	20	21	19.26
Germany	Frankfurt	20	22	18.71
Germany	Frankfurt	20	23	17.93
Germany	Frankfurt	21	0	17.22
Germany	Frankfurt	21	1	17.01
Germany	Frankfurt	21	2	16.28
Germany	Frankfurt	21	3	15.46
Germany	Frankfurt	21	4	14.7
Germany	Frankfurt	21	5	14.09
Germany	Frankfurt	21	6	13.93
Germany	Frankfurt	21	7	15.04
Germany	Frankfurt	21	8	17.26
Germany	Frankfurt	21	9	19.16
Germany	Frankfurt	22	10	22.53
Germany	Frankfurt	22	11	24.5
Germany	Frankfurt	22	12	26.14
Germany	Frankfurt	22	13	27.48
Germany	Frankfurt	22	14	28.41
Germany	Frankfurt	22	15	29.13
Germany	Frankfurt	22	16	29.61
Germany	Frankfurt	22	17	30.02
Germany	Frankfurt	22	18	30.1
Germany	Frankfurt	22	19	29.48
Germany	Frankfurt	22	20	28.14
Germany	Frankfurt	22	21	24.45
Germany	Frankfurt	22	22	22.01
Germany	Frankfurt	22	23	20.94
Germany	Frankfurt	23	0	19.69
Germany	Frankfurt	23	1	18.71
Germany	Frankfurt	23	2	17.89
Germany	Frankfurt	23	3	16.57
Germany	Frankfurt	23	4	15.58
Germany	Frankfurt	23	5	14.8
Germany	Frankfurt	23	6	14.43
Germany	Frankfurt	23	7	17.86
Germany	Frankfurt	23	8	22.07
Germany	Frankfurt	23	9	25.09
Germany	Frankfurt	23	10	27.29
Germany	Frankfurt	23	14	33.04
Germany	Frankfurt	23	15	33.76
Germany	Frankfurt	23	16	34.15
Germany	Frankfurt	23	17	34.34
Germany	Frankfurt	23	18	34.26
Germany	Frankfurt	24	12	29.26
Germany	Frankfurt	24	13	29.15
Germany	Frankfurt	24	14	29.82
Germany	Frankfurt	24	15	29.85
Germany	Frankfurt	24	16	29.44
Germany	Frankfurt	24	17	28.47
Germany	Frankfurt	24	18	26.85
Germany	Frankfurt	24	19	24.72
Germany	Frankfurt	24	20	21.18
Germany	Frankfurt	24	21	17.77
Germany	Frankfurt	24	22	16.79
Germany	Frankfurt	24	23	16.2
UK	London	10	0	12.4
UK	London	10	1	11.8
UK	London	10	2	11.2
UK	London	10	8	11.8
UK	London	10	16	20
UK	London	10	17	19.4
UK	London	10	18	19.1
UK	London	10	19	18.8
UK	London	10	20	18.4
UK	London	10	21	17.2
UK	London	10	22	15.9
UK	London	10	23	15.2
UK	London	11	0	14.8
UK	London	11	1	13.1
UK	London	11	2	11.6
UK	London	11	3	10.2
UK	London	11	10	15.8
UK	London	11	11	17
UK	London	11	12	16.4
UK	London	11	13	15.7
UK	London	11	14	16
UK	London	11	15	16.8
UK	London	11	16	14.9
UK	London	11	17	14.2
UK	London	11	18	13.3
UK	London	12	16	10.2
UK	London	12	17	9.9
UK	London	12	18	9.8
UK	London	12	19	10.1
UK	London	12	20	10.3
UK	London	12	21	9.9
UK	London	12	22	9.5
UK	London	12	23	9.4
UK	London	13	0	9.5
UK	London	13	1	9.7
UK	London	13	2	9.9
UK	London	13	3	10
UK	London	13	4	9.7
UK	London	13	5	8.9
UK	London	13	6	9.6
UK	London	13	7	10.2
UK	London	13	8	11.2
UK	London	14	3	12.4
UK	London	14	4	11.8
UK	London	14	5	11.3
UK	London	14	6	11.1
UK	London	14	7	12
UK	London	14	8	13.8
UK	London	14	9	15.3
UK	London	14	10	16.3
UK	London	14	11	17.3
UK	London	14	12	18.1
UK	London	14	13	18.9
UK	London	15	4	9.6
UK	London	15	5	8.8
UK	London	15	6	8.6
UK	London	15	7	11.4
UK	London	15	10	18
UK	London	15	11	19.6
UK	London	15	12	20.9
UK	London	15	13	22
UK	London	15	14	22.7
UK	London	15	15	23.3
UK	London	15	16	23.7
UK	London	15	17	24
UK	London	15	22	17.6
UK	London	15	23	16.8
UK	London	16	0	15.8
UK	London	16	1	15
UK	London	16	2	14.3
UK	London	16	3	13.3
UK	London	16	4	12.5
UK	London	16	5	11.8
UK	London	16	6	11.5
UK	London	16	16	27.3
UK	London	16	17	27.5
UK	London	16	18	27.4
UK	London	16	19	26.9
UK	London	16	20	25.7
UK	London	16	21	23.8
UK	London	16	22	22.2
UK	London	16	23	20.6
UK	London	17	0	19.3
UK	London	17	1	17.9
UK	London	17	2	16.9
UK	London	17	10	18.9
UK	London	17	11	22.1
UK	London	17	12	23.4
UK	London	17	13	23.3
UK	London	17	14	23.9
UK	London	17	15	23.9
UK	London	17	16	23.6
UK	London	17	17	22.8

[Anonymous]

Zubair,

Thanks a lot for your solution. I will let you know if that works on my dataset or not.

Best

Qmars