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.
I have 4 Categories (GP, ID, Age, Date). I would would like to create calculated column and group by GP, ID, and Age and Rank/ count by Date to see how many months each member has in past 24 month.
My Code works until I have members who cancelled their membership for a few months and then resumed after. I need to restart from the first month after skip. for example :
GROUP | ID | AGE | DATE | Current Rank | Desired RANK |
1 | 220 | 35-44 | 202206 | 12 | 6 |
1 | 220 | 35-44 | 202205 | 12 | 5 |
1 | 220 | 35-44 | 202204 | 12 | 4 |
1 | 220 | 35-44 | 202203 | 12 | 3 |
1 | 220 | 35-44 | 202202 | 12 | 2 |
1 | 220 | 35-44 | 202201 | 12 | 1 |
1 | 220 | 35-44 | 202012 | 24 | 24 |
1 | 220 | 35-44 | 202011 | 23 | 23 |
1 | 220 | 35-44 | 202010 | 22 | 22 |
1 | 220 | 35-44 | 202009 | 21 | 21 |
1 | 220 | 35-44 | 202008 | 20 | 20 |
1 | 220 | 35-44 | 202007 | 19 | 19 |
1 | 220 | 35-44 | 202006 | 18 | 18 |
1 | 220 | 35-44 | 202005 | 17 | 17 |
1 | 220 | 35-44 | 202004 | 16 | 16 |
… | … | … | … | … | … |
1 | 220 | 35-44 | 201901 | 1 | 1 |
This is what I have tried but doesn't work for dates skipping.
RKING= RANKX ( CALCULATETABLE ( VALUES ('tbl'[Date] ), ALLEXCEPT ( 'tblW', 'tbl'[GP], 'tbl'[ID] ), 'tbl'[AGE] = 'tbl'[AGE], 'tbl'[date] >= start_date && 'tbl'[date] <= end_date // date slicer ), [Date] , ,ASC )
Solved! Go to Solution.
OH OH sorry,
I didnt get what your mean.
RKING=
RANKX (
Filter(
'tblW',
'tbl'[AGE] = EARLIER('tbl'[AGE]) &&
'tbl'[ID] = EARLIER('tbl'[ID]) &&
'tbl'[GROUP] = EARLIER('tbl'[GROUP])
),
'tbl'[DATE],
,
ASC
)
OH OH sorry,
I didnt get what your mean.
Hi,
I am not sure how your data model looks like, but I tried to create a sample pbix file like below.
Please check the below picture and the attached file.
I hope this can provide some ideas on creating a solution to your problem.
step one Previousmonthmembership: =
VAR _currentdate =
MAX ( Data[DATE] )
VAR _currentgroup =
MAX ( Data[GROUP] )
VAR _currentid =
MAX ( Data[ID] )
VAR _currentage =
MAX ( Data[AGE] )
RETURN
MAXX (
FILTER (
ALLSELECTED ( Data ),
Data[GROUP] = _currentgroup
&& Data[ID] = _currentid
&& Data[AGE] = _currentage
&& Data[DATE] < _currentdate
),
Data[DATE]
)
step two currentgroupindex: =
VAR _currentdate =
MAX ( Data[DATE] )
VAR _currentgroup =
MAX ( Data[GROUP] )
VAR _currentid =
MAX ( Data[ID] )
VAR _currentage =
MAX ( Data[AGE] )
VAR _addindexcolumn =
ADDCOLUMNS (
ADDCOLUMNS (
ALLSELECTED ( Data ),
"@memberpreviousmonth", [step one Previousmonthmembership:]
),
"@consecutivecondition",
SWITCH (
TRUE (),
[@memberpreviousmonth] = BLANK (), 0,
Data[DATE] - [@memberpreviousmonth] > 31, 1,
0
)
)
VAR _addgroupingcolumn =
ADDCOLUMNS (
_addindexcolumn,
"@groupindex",
SUMX (
FILTER ( _addindexcolumn, Data[DATE] <= _currentdate ),
[@consecutivecondition]
)
)
VAR _currentgroupindex =
MAXX ( FILTER ( _addgroupingcolumn, Data[DATE] = _currentdate ), [@groupindex] )
RETURN
_currentgroupindex
step three Ranking measure: =
VAR _currentgroupindex = [step two currentgroupindex:]
RETURN
IF (
COUNTROWS ( Data ) <> BLANK (),
RANKX (
FILTER ( ALLSELECTED ( Data ), [step two currentgroupindex:] = _currentgroupindex ),
CALCULATE ( MAX ( Data[DATE] ) ),
,
ASC
)
)
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.
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 |
---|---|
37 | |
25 | |
19 | |
14 | |
8 |
User | Count |
---|---|
68 | |
48 | |
45 | |
18 | |
15 |