Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Problem grouping by days

I have a two tables, one with data everyfive minutes in 1200 locations and a calendar with different grouped dates. 

 

I want to be able to analyse the number of consecutive days with 0 values and for a given day the number of locations with 0 values for that day.

I have tried some approachs with addcolumns and groupby with no success. I was wondering whether someone could give me some clues on how to approach it

 

TimeLocationData
31/02/2021 00:0010.2
31/02/2021 00:0510
31/02/2021 00:0020.2
31/02/2021 00:0520
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    I suggest you to add a date only column in your data table. It will help us to group the table in calculation.

    Datevalue = DATEVALUE('Table'[Time])

    Try this code.

    Measure = 
VAR _T = 
SUMMARIZE(ALL('Table'),'Table'[Datevalue],"Count",CALCULATE(DISTINCTCOUNT('Table'[Value]),ALLEXCEPT('Table','Table'[Datevalue])),"Max",CALCULATE(MAX('Table'[Value]),ALLEXCEPT('Table','Table'[Datevalue])))
RETURN
COUNTAX(FILTER(_T,[Datevalue]<=MAX('Table'[Datevalue])&&[Count] = 1&&[Max] = 0),[Datevalue]) +0

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

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

     

4 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    Your sample data only covers one day, and it specifies a day that doesn't exist.  Please provide better sample data that fully covers your issue.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      This is a more complete data. Basically I have a lot of locations and I would like to see how many consecutive days do I have with 0 data for a whole day in all locations

       

      Time Location Value
      01/01/2022 00:00 10.455741
      01/01/2022 06:00 10
      01/01/2022 12:00 10
      01/01/2022 18:00 11
      02/01/2022 00:00 10
      02/01/2022 06:00 10
      02/01/2022 12:00 10
      02/01/2022 18:00 10
      03/01/2022 00:00 10
      03/01/2022 06:00 10
      03/01/2022 12:00 10
      03/01/2022 18:00 10
      01/01/2022 00:00 20.433114
      01/01/2022 06:00 20.778561
      01/01/2022 12:00 20.961754
      01/01/2022 18:00 20.878894
      02/01/2022 00:00 20
      02/01/2022 06:00 20
      02/01/2022 12:00 20
      02/01/2022 18:00 20
      03/01/2022 00:00 20
      03/01/2022 06:00 20
      03/01/2022 12:00 20
      03/01/2022 18:00 20

      The output I am loo king for is a measure with the consecutive 0 values as below:

      Date Measure
      01/01/2022 0
      02/01/2022 1
      03/01/2022 2
      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi Anonymous ,

         

        I suggest you to add a date only column in your data table. It will help us to group the table in calculation.

        Datevalue = DATEVALUE('Table'[Time])

        Try this code.

        Measure = 
VAR _T = 
SUMMARIZE(ALL('Table'),'Table'[Datevalue],"Count",CALCULATE(DISTINCTCOUNT('Table'[Value]),ALLEXCEPT('Table','Table'[Datevalue])),"Max",CALCULATE(MAX('Table'[Value]),ALLEXCEPT('Table','Table'[Datevalue])))
RETURN
COUNTAX(FILTER(_T,[Datevalue]<=MAX('Table'[Datevalue])&&[Count] = 1&&[Max] = 0),[Datevalue]) +0

        Result is as below.

         

        Best Regards,
        Rico Zhou

         

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

         

  • rbriga's avatar
    rbriga
    Impactful Individual
    4 years ago

    Add a date- not datetime- column to your fact table. You can do that either:

    1. At the source (for example, a native SQL query)
    2. In PowerQuery, for example: 

    Now you can connent this new "Date" column to your calendar table with a relationship, and perform some DAX.

    This will work when the visual table uses the Calendar [Date] field:

    Number of Locations with Zero=
SUMX(VALUES('Table'[Locations),
IF(SUM('Table'[Data]>0,
1,
BLANK()
)