Forum Discussion

stribor45's avatar
stribor45
Post Prodigy
2 years ago

Summary and Ranking

This code works for me as is but I would like to narrow it down based on the date. When I try to add another condition to the filter which is Table_A[Call Date] IN DATESBETWEEN "date1" and date1"  I ...
Help
stribor45's avatar
stribor45
Post Prodigy
2 years ago

This works but I noticed that is N/A somehow has rank 1 it will be excluded from the resulting table

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi stribor45 ,

 

Try it.

 

 

DEFINE
    VAR _date1=DATE(2023,1,1)  //You can change this date variable to suit your needs
    VAR _date2=DATE(2023,1,3)  ////You can change this date variable to suit your needs
    VAR REPS =
      FILTER (
        ADDCOLUMNS (
            SUMMARIZE (
                FILTER (
                    'Table_A',
                    
                   'Table_A'[Call Date] IN DATESBETWEEN('Table_A'[Call Date],_date1, _date2)
                ),
                'Table_A'[Name]
            ),
            "@count", CALCULATE ( COUNT ( 'Table_A'[ID] ) ),
            "@rank",
                RANKX (
                    ALL ( 'Table_A'[Name] ),
                    CALCULATE ( COUNT ( 'Table_A'[ID] ) ),
                    ,
                    DESC,
                    DENSE
                )
        ), //addcolumns
        [@rank] = 1 || [@rank] = 2 )
      
       
EVALUATE
REPS

 

 

Or:

 

 

DEFINE
    VAR _date1=DATE(2023,1,1)  //You can change this date variable to suit your needs

    VAR REPS =
      FILTER (
        ADDCOLUMNS (
            SUMMARIZE (
                FILTER (
                    'Table_A',
                   
                   'Table_A'[Call Date] =_date1
                ),
                'Table_A'[Name]
            ),
            "@count", CALCULATE ( COUNT ( 'Table_A'[ID] ) ),
            "@rank",
                RANKX (
                    ALL ( 'Table_A'[Name] ),
                    CALCULATE ( COUNT ( 'Table_A'[ID] ) ),
                    ,
                    DESC,
                    DENSE
                )
        ), //addcolumns
        [@rank] = 1 || [@rank] = 2 )
      
       
EVALUATE
REPS

 

 

If I've misunderstood you, please accept the current post answer as a solution and create a new post detailing your new questions and expected results, thanks in advance.

 

 

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.