Forum Discussion

Alopez11's avatar
Alopez11
Regular Visitor
4 years ago

Count Data, Help Again

Hello, I need a hand with a Dax formula
I got the following data set:

Date          Time    T°   
1-1-2011   0:30     3

1-1-2011   01:00   3,5
1-1-2011   01:30   3
1-1-2011   02.00   3

Etc

I've made the following chart

 

With the Max and Min temperature of each day and 2 min and max lines at 10°C and 20°C

Now, what I need is to count all the days that the min temperature is over or equal to 10°C and the day before the max temperature was over or equal to 20°C to display in a labbel

Thanks you very much for the help

6 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Alopez11 ,

     

    Please try the following formula to create a measure:

    Flag = CALCULATE(DISTINCTCOUNT(Hoja1[fecha]),FILTER('Hoja1',10<=MIN('Hoja1'[T°]) && 20<=MAX('Hoja1'[T°])))

    And apply it to filter pane to only show the date whose min>=10 and max>=20:

     

    If you want to calculate the count of days, please use:

    Count days = SUMX(VALUES(Hoja1[fecha]),[Flag])

     

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Alopez11's avatar
    Alopez11
    Regular Visitor
    4 years ago

    Hello Evelyn
    Anonymous 

    That formula works well, but we have 1 problem.
    I need to count the "events" that the day before the max temperature is greater than 20°C and the min temperature is greater than 10°C

    Example


    Thanks!

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Alopez11 ,

     

    So based on your data sample, the output should be:

    If so, please modify the Flag measure:

    Flag = 
var _min=CALCULATE(MIN('Hoja1'[T°]),FILTER(ALL(Hoja1),[fecha]=MAX('Hoja1'[fecha])-1))
var _max=CALCULATE(MAX('Hoja1'[T°]),FILTER(ALL(Hoja1),[fecha]=MAX('Hoja1'[fecha])-1))
return 
CALCULATE(DISTINCTCOUNT(Hoja1[fecha]),FILTER('Hoja1',10<=_min && 20<=_max))

     

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Alopez11's avatar
      Alopez11
      Regular Visitor
      4 years ago

      Anonymous 
      I'm really thankfull for your help,
      I have to adapt a little bit the formula and i mannage to get the right flags, but I need to count hoy many flags I have in a labbel so I can tell, this month we hace 5 flags in example. How can I do that?

      Flag =
      var _min='Base de Datos'[T° Min]
      var _max=CALCULATE(MAX('Base de datos'[T°]),filter(ALLSELECTED('Base de Datos'),'Base de Datos'[fecha]=max('Base de Datos'[fecha])-1))
      return
      CALCULATE(DISTINCTCOUNT('Base de datos'[fecha]),FILTER('Base de datos',10<=_min&&20<=_max)
      )