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 ...
  • 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.

     

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