Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have a input table in this format,
Date | Name | Present or Absent |
5/1/2022 | A | Present |
5/1/2022 | B | Present |
5/2/2022 | A | Present |
5/2/2022 | B | Present |
5/3/2022 | A | Present |
5/3/2022 | B | Present |
5/4/2022 | A | Present |
5/4/2022 | B | Absent |
5/5/2022 | A | Present |
5/5/2022 | B | Present |
5/6/2022 | A | Absent |
5/6/2022 | B | Present |
I need to generate a final visualization like below, Not sure how to calculate Highest No. of days Continously Present column
Name | Highest No.of days Continuously Present |
A | 5 |
B | 3 |
Can anyone help me to get this column using Calculated Columns or Measures?
Any help is really appreciated
Solved! Go to Solution.
Hi @Iam_Uday
Here is a soluation based on 2 columns and a measure https://www.dropbox.com/t/y15kpJR7MzVRVhsf
Calculated Column (Rank):
Rank =
VAR Rank1 =
RANKX (
CALCULATETABLE ( Attendance, ALLEXCEPT ( Attendance, Attendance[Name] ) ),
Attendance[Date],, ASC, Dense
)
VAR Rank2 =
RANKX (
CALCULATETABLE (
Attendance, ALLEXCEPT ( Attendance, Attendance[Name], Attendance[Present or Absent] ) ),
Attendance[Date],, ASC, Dense
)
RETURN
IF (
Attendance[Present or Absent] <> "Absent",
Rank1 - Rank2
)
Calculated Column (# Days Present)
# Days Present =
IF (
NOT ISBLANK ( Attendance[Rank] ),
COUNTROWS (
CALCULATETABLE (
Attendance,
ALLEXCEPT ( Attendance, Attendance[Name], Attendance[Rank] )
)
)
)
Measure
Max # Days Present = MAX ( Attendance[# Days Present] )
Hi @Iam_Uday
Here is a soluation based on 2 columns and a measure https://www.dropbox.com/t/y15kpJR7MzVRVhsf
Calculated Column (Rank):
Rank =
VAR Rank1 =
RANKX (
CALCULATETABLE ( Attendance, ALLEXCEPT ( Attendance, Attendance[Name] ) ),
Attendance[Date],, ASC, Dense
)
VAR Rank2 =
RANKX (
CALCULATETABLE (
Attendance, ALLEXCEPT ( Attendance, Attendance[Name], Attendance[Present or Absent] ) ),
Attendance[Date],, ASC, Dense
)
RETURN
IF (
Attendance[Present or Absent] <> "Absent",
Rank1 - Rank2
)
Calculated Column (# Days Present)
# Days Present =
IF (
NOT ISBLANK ( Attendance[Rank] ),
COUNTROWS (
CALCULATETABLE (
Attendance,
ALLEXCEPT ( Attendance, Attendance[Name], Attendance[Rank] )
)
)
)
Measure
Max # Days Present = MAX ( Attendance[# Days Present] )
Hi,
I tried to create a sample pbix file like the below.
I numbered the measures to follow step by step.
Please check the attached pbix file.
1 P or A measure: =
IF( SELECTEDVALUE(Data[PorA]) = "P", 0, 1)
2 Index measure: =
CALCULATE (
SUMX ( SUMMARIZE ( Data, 'Name'[Name], 'Calendar'[Date] ), [1 P or A measure:] ),
'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
)
3 Max of Countrow same index: =
IF( ISFILTERED('Name'[Name] ),
MAXX (
GROUPBY (
ADDCOLUMNS (
FILTER (
SUMMARIZE ( ALL ( Data ), 'Name'[Name], 'Calendar'[Date] ),
'Name'[Name] = MAX ( 'Name'[Name] )
),
"@indexmeasure", [2 Index measure:]
),
[@indexmeasure],
"@countrow", SUMX ( CURRENTGROUP (), 1 )
),
[@countrow]
) - 1)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Thanks @Jihwan_Kim for taking time and providing the solution, But for my real data which is close to 1M Rows. This solution of creating measures is not working.
When I get other columns in to the table (Which describe the fact's on Name) - Power Bi is running out of memory and even final measure to show No. of max days a student is present consecutively is taking quite a bit of time.
Here's a measure but I still have to test/debug it. If you want to try while it's in the making... This is averaging the max streaks over the names but if you drop the names on the canvas, it'll give you exactly what you want - the longest streak for the name. Please note there's no CALCULATE in there, so if you have thousands of Names in a visual it should be quick.
EDIT: OK, I've checked it - does work. Now try to see how long it'll take on your dataset.
EDIT2: It takes milliseconds to calculate this measure on a set with 1M rows.
Avg Max Days Present =
AVERAGEX(
VALUES( T[Name] ),
var CurrentName = T[Name]
var SubsetOfinterest =
FILTER(
T,
T[Name] = CurrentName
&&
T[Present or Absent] = "present"
)
var MinDate =
MINX( SubsetOfinterest, T[Date] )
var SubsetOfinterestWithOrder =
ADDCOLUMNS(
SubsetOfinterest,
"@Grouper",
var CurrentDate = T[Date]
var RankStartingWith0 =
RANKX(
SubsetOfinterest,
T[Date],
CurrentDate,
ASC,
DENSE
) - 1
return
( MinDate + RankStartingWith0 )
- CurrentDate
)
var Groupings =
GROUPBY(
SubsetOfinterestWithOrder,
[@Grouper],
"@RowCount", SUMX( CURRENTGROUP(), 1 )
)
var MaxStreak =
MAXX(
Groupings,
[@RowCount]
)
return
MaxStreak
)
User | Count |
---|---|
47 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |