Forum Discussion

TechR21's avatar
TechR21
Icon for Helper V rankHelper V
3 years ago

Selecting most recent record in table based on date

Hi,   Im creating a report based on a "table" visual (not a matrix) . This visual has following columns (+ 10 others that are not important for this case). The report is basically a big dump of spe...
Need Help
TKCillie's avatar
TKCillie
Regular Visitor
1 year ago

Hello, I am trying to find the most recent consent change by datetime, but also by channel, I am struggling with the subchannel seperation:

 

This is what I used, it returns the last entry YES but not by Subchannel:

Most Recent Entry =
Var _consentID = SELECTEDVALUE(Consent[Account ID 18 Char])

Var _table = FILTER(
                ADDCOLUMNS(DISTINCT(SELECTCOLUMNS(Consent,"ID",[Account ID 18 Char], "MOD", Consent[Capture Datetime].[Date])), "Rank", CALCULATE(
                COUNTROWS('Consent'),
                FILTER(
                    ALL(Consent),
                    'Consent'[Account ID 18 Char] = EARLIER([ID]) &&
                    'Consent'[Capture Datetime].[Date] > EARLIER([MOD])
                    )
                    )+1),
                    [Rank] =1)
RETURN
TOPN(1,SELECTCOLUMNS( FILTER(_table, [Rank] =1 && [ID] = _consentID), "x",[Rank]))