topic Re: Problem grouping by days in DAX Commands and Tips

Problem grouping by days

Anonymous — Thu, 31 Mar 2022 10:58:29 GMT

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

Re: Problem grouping by days

lbendlin — Sun, 03 Apr 2022 00:13:21 GMT

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.

Re: Problem grouping by days

rbriga — Sun, 03 Apr 2022 08:11:16 GMT

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

At the source (for example, a native SQL query)
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() )

Re: Problem grouping by days

Anonymous — Mon, 04 Apr 2022 07:58:38 GMT

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

Re: Problem grouping by days

Anonymous — Tue, 05 Apr 2022 02:23:07 GMT

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.