Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Using RankX and AddColumn to a Virtual Table

I am trying to create a formula that will first rank the data based on the [Date-Calendar] column grouped by the Claim Num (Full), then count the rows where the Rank = 1. 

 

Here is my formula:

 

 

Test = 

VAR vTable = 
    ADDCOLUMNS(
        SUMMARIZE('FACT Daily Claims', 
            'FACT Daily Claims'[Claim Num (Full)], 
            'FACT Daily Claims'[Date - Calendar], 
            'FACT Daily Claims'[Claim Status (Date)]
        ),
        "Count",
            1,
        "Rank by Group",
            RANKX(
                FILTER(ALL('FACT Daily Claims'), 'FACT Daily Claims'[Claim Num (Full)] = vClaimNum),
                [Date - Calendar],,DESC
            )
    )

RETURN
COUNTROWS(
    FILTER(vTable, 
        [Rank by Group] = 1
    )
)

 

 

 

Sample Data would be

Claim Num (Full)Date - CalendarClaim Status (Date)
11/10/2021Open
21/8/2021Open
21/19/2021Open
11/15/2021Closed

 

I need the value to return 2. 

2 Replies

  • AlB's avatar
    AlB
    Community Champion

    Hi Anonymous 

    I'm not sure I follow. How is this: 

          first rank the data based on the [Date-Calendar] column grouped by the Claim Num (Full), then count the rows where the Rank = 1

    different from  simply doing a DISTINCTCOUNT  of Claim Num Full, i.e. the number of groups??

     

    Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers