Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
@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 () )
)
)
@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 () )
)
)
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"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
25 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |