Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

conditional count

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 r...
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    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])


     

Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago

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

CountryTownDayHourTemperature
GermanyFrankfurt17015.49
GermanyFrankfurt17114.72
GermanyFrankfurt17214.04
GermanyFrankfurt17814.76
GermanyFrankfurt17916.91
GermanyFrankfurt171018.58
GermanyFrankfurt171120.18
GermanyFrankfurt171221.57
GermanyFrankfurt171322.8
GermanyFrankfurt171423.84
GermanyFrankfurt171524.58
GermanyFrankfurt181618.63
GermanyFrankfurt181717.71
GermanyFrankfurt181816.58
GermanyFrankfurt181915.39
GermanyFrankfurt182014.56
GermanyFrankfurt182113.49
GermanyFrankfurt182212.59
GermanyFrankfurt182312.69
GermanyFrankfurt19611.28
GermanyFrankfurt19712.17
GermanyFrankfurt19813.08
GermanyFrankfurt19914.41
GermanyFrankfurt191015.59
GermanyFrankfurt191115.73
GermanyFrankfurt191215.5
GermanyFrankfurt191314.8
GermanyFrankfurt191414.4
GermanyFrankfurt191513.51
GermanyFrankfurt20511.17
GermanyFrankfurt20611.99
GermanyFrankfurt20712.71
GermanyFrankfurt20813.94
GermanyFrankfurt20916.19
GermanyFrankfurt201018.24
GermanyFrankfurt201621.3
GermanyFrankfurt201721.95
GermanyFrankfurt201821.13
GermanyFrankfurt201921.1
GermanyFrankfurt202020.56
GermanyFrankfurt202119.26
GermanyFrankfurt202218.71
GermanyFrankfurt202317.93
GermanyFrankfurt21017.22
GermanyFrankfurt21117.01
GermanyFrankfurt21216.28
GermanyFrankfurt21315.46
GermanyFrankfurt21414.7
GermanyFrankfurt21514.09
GermanyFrankfurt21613.93
GermanyFrankfurt21715.04
GermanyFrankfurt21817.26
GermanyFrankfurt21919.16
GermanyFrankfurt221022.53
GermanyFrankfurt221124.5
GermanyFrankfurt221226.14
GermanyFrankfurt221327.48
GermanyFrankfurt221428.41
GermanyFrankfurt221529.13
GermanyFrankfurt221629.61
GermanyFrankfurt221730.02
GermanyFrankfurt221830.1
GermanyFrankfurt221929.48
GermanyFrankfurt222028.14
GermanyFrankfurt222124.45
GermanyFrankfurt222222.01
GermanyFrankfurt222320.94
GermanyFrankfurt23019.69
GermanyFrankfurt23118.71
GermanyFrankfurt23217.89
GermanyFrankfurt23316.57
GermanyFrankfurt23415.58
GermanyFrankfurt23514.8
GermanyFrankfurt23614.43
GermanyFrankfurt23717.86
GermanyFrankfurt23822.07
GermanyFrankfurt23925.09
GermanyFrankfurt231027.29
GermanyFrankfurt231433.04
GermanyFrankfurt231533.76
GermanyFrankfurt231634.15
GermanyFrankfurt231734.34
GermanyFrankfurt231834.26
GermanyFrankfurt241229.26
GermanyFrankfurt241329.15
GermanyFrankfurt241429.82
GermanyFrankfurt241529.85
GermanyFrankfurt241629.44
GermanyFrankfurt241728.47
GermanyFrankfurt241826.85
GermanyFrankfurt241924.72
GermanyFrankfurt242021.18
GermanyFrankfurt242117.77
GermanyFrankfurt242216.79
GermanyFrankfurt242316.2
UKLondon10012.4
UKLondon10111.8
UKLondon10211.2
UKLondon10811.8
UKLondon101620
UKLondon101719.4
UKLondon101819.1
UKLondon101918.8
UKLondon102018.4
UKLondon102117.2
UKLondon102215.9
UKLondon102315.2
UKLondon11014.8
UKLondon11113.1
UKLondon11211.6
UKLondon11310.2
UKLondon111015.8
UKLondon111117
UKLondon111216.4
UKLondon111315.7
UKLondon111416
UKLondon111516.8
UKLondon111614.9
UKLondon111714.2
UKLondon111813.3
UKLondon121610.2
UKLondon12179.9
UKLondon12189.8
UKLondon121910.1
UKLondon122010.3
UKLondon12219.9
UKLondon12229.5
UKLondon12239.4
UKLondon1309.5
UKLondon1319.7
UKLondon1329.9
UKLondon13310
UKLondon1349.7
UKLondon1358.9
UKLondon1369.6
UKLondon13710.2
UKLondon13811.2
UKLondon14312.4
UKLondon14411.8
UKLondon14511.3
UKLondon14611.1
UKLondon14712
UKLondon14813.8
UKLondon14915.3
UKLondon141016.3
UKLondon141117.3
UKLondon141218.1
UKLondon141318.9
UKLondon1549.6
UKLondon1558.8
UKLondon1568.6
UKLondon15711.4
UKLondon151018
UKLondon151119.6
UKLondon151220.9
UKLondon151322
UKLondon151422.7
UKLondon151523.3
UKLondon151623.7
UKLondon151724
UKLondon152217.6
UKLondon152316.8
UKLondon16015.8
UKLondon16115
UKLondon16214.3
UKLondon16313.3
UKLondon16412.5
UKLondon16511.8
UKLondon16611.5
UKLondon161627.3
UKLondon161727.5
UKLondon161827.4
UKLondon161926.9
UKLondon162025.7
UKLondon162123.8
UKLondon162222.2
UKLondon162320.6
UKLondon17019.3
UKLondon17117.9
UKLondon17216.9
UKLondon171018.9
UKLondon171122.1
UKLondon171223.4
UKLondon171323.3
UKLondon171423.9
UKLondon171523.9
UKLondon171623.6
UKLondon171722.8