Summary and Ranking

[stribor45]

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 get the error "single value for date can't be determined.  I feel like this has something to do with row context. How would I add that condition as well to my existing code?

DEFINE
    VAR REPS =
      FILTER (
        ADDCOLUMNS (
            SUMMARIZE (
                FILTER (
                    'Table_A',
                    'Table_A'[Name] <> "N/A"
                ),
                '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

[Anonymous]

Hi @stribor45 ,

Please try:

If you need a date range:

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'[Name] <> "N/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

If you need to equal a single date:

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'[Name] <> "N/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

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. 

[stribor45]

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

[Anonymous]

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. 

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...