topic Re: Counting number of continuos same entries in DAX Commands and Tips

Counting number of continuos same entries

saurav_0101 — Sat, 17 Oct 2020 10:32:26 GMT

Hello All,

I am trying to count number of continuous same entries. Please see below example

		Expected Outcome
Date	Pet	Number of days
1/1/2020	Cat	1
1/2/2020	Cat	2
1/3/2020	Cat	3
1/4/2020	Cat	4
1/5/2020	Dog	1
1/6/2020	Dog	2
1/7/2020	Dog	3
1/8/2020	Rat	1
1/9/2020	Cat	1
1/10/2020	Cat	2
1/11/2020	Rabbit	1
1/12/2020	Rabbit	2
1/13/2020	Rabbit	3
1/14/2020	Rabbit	4
1/15/2020	Rabbit	5
1/16/2020	Rabbit	6
1/17/2020	Cat	1
1/18/2020	Cat	2
1/19/2020	Dog	1
1/20/2020	Dog	2
1/21/2020	Dog	3
1/22/2020	Dog	4
1/23/2020	Rat	1
1/24/2020	Rat	2
1/25/2020	Rat	3
1/26/2020	Rat	4
1/27/2020	Dog	1
1/28/2020	Cat	1
1/29/2020	Cat	2
1/30/2020	Dog	1
1/31/2020	Rabbit	1
2/1/2020	Rabbit	2
2/2/2020	Rabbit	3
2/3/2020	Rabbit	4
2/4/2020	Rabbit	5
2/5/2020	Cat	1
2/6/2020	Cat	2
2/7/2020	Cat	3
2/8/2020	Cat	4
2/9/2020	Dog	1
2/10/2020	Dog	2
2/11/2020	Dog	3
2/12/2020	Rat	1
2/13/2020	Cat	1
2/14/2020	Dog	1

As it can be seen in the third column (Expected outcome), it says number of days. Logic is that the it shows number of days the pet was same.

Like on 3rd Jan, cat pet was there for 3 days, 4th jan it was for 4 days. But on 9th Jan it was for 1 day and 10th Jan it was for 2 days.

Please suggest if any DAX command can help.

Thanks in advance.

Re: Counting number of continuos same entries

CNENFRNL — Sat, 17 Oct 2020 10:52:36 GMT

Hi, saurav_0101 , Maybe a solution by Power Query is way much easier, I think. You may want to try,

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdM7DsIwGAPgu2Su9CfOo+0MJ2CtOtAFMbFwfyEQeVT2+kmJ4trdNhcsGDy8m9zl/nb79CUwRabElCtdX48/FaaZaal0a3etfH3wwkI/ehzPxtAcNSfNWXPRPIvXLcJWzg8vLAiDsMgfD0lYFlaEiX4gckA0FEWOqBqCaVW1wVRrMFUa+gbrq9A32Im6glFEGDWFYYODUVM4DbBFpr8Iw/R+Z/cP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Pet = _t]), #"Grouped Rows" = Table.Group(Source, {"Pet"}, {{"All", each Table.AddIndexColumn(_,"Consecutive Days",1,1)}}, GroupKind.Local), #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Pet"}), #"Expanded All" = Table.ExpandTableColumn(#"Removed Columns", "All", {"Date", "Pet", "Consecutive Days"}, {"Date", "Pet", "Consecutive Days"}) in #"Expanded All"

Re: Counting number of continuos same entries

wdx223_Daniel — Tue, 20 Oct 2020 00:22:17 GMT

saurav_0101 try this code, it might help you. you need a date table, and link it ot FactTable, date to date.

Distinct_Continuious_Count := VAR _CurrentDate = MAX ( Dates[Date] ) VAR _MinDate = MAXX ( FILTER ( ALL ( Dates[Date] ), Dates[Date] < _CurrentDate && CALCULATE ( ISEMPTY ( FactTable ) ) ), Dates[Date] ) RETURN IF ( COUNTROWS ( FactTable ), CALCULATE ( COUNT ( Dates[Date] ), Dates[Date] <= _CurrentDate && IF ( _MinDate, Dates[Date] > _MinDate, TRUE () ) ) )