Problem grouping by days

[Anonymous]

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

Time	Location	Data
31/02/2021 00:00	1	0.2
31/02/2021 00:05	1	0
31/02/2021 00:00	2	0.2
31/02/2021 00:05	2	0

[rbriga]

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

[lbendlin]

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]

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	1	0.455741
01/01/2022 06:00	1	0
01/01/2022 12:00	1	0
01/01/2022 18:00	1	1
02/01/2022 00:00	1	0
02/01/2022 06:00	1	0
02/01/2022 12:00	1	0
02/01/2022 18:00	1	0
03/01/2022 00:00	1	0
03/01/2022 06:00	1	0
03/01/2022 12:00	1	0
03/01/2022 18:00	1	0
01/01/2022 00:00	2	0.433114
01/01/2022 06:00	2	0.778561
01/01/2022 12:00	2	0.961754
01/01/2022 18:00	2	0.878894
02/01/2022 00:00	2	0
02/01/2022 06:00	2	0
02/01/2022 12:00	2	0
02/01/2022 18:00	2	0
03/01/2022 00:00	2	0
03/01/2022 06:00	2	0
03/01/2022 12:00	2	0
03/01/2022 18:00	2	0

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 · Accepted solution]

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.