topic Re: Summary and Ranking in DAX Commands and Tips

Summary and Ranking

stribor45 — Fri, 26 Jan 2024 16:44:08 GMT

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

Re: Summary and Ranking

lbendlin — Sat, 27 Jan 2024 02:58:38 GMT

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-Forum/ba-p/963216

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/1447523

Re: Summary and Ranking

Anonymous — Mon, 29 Jan 2024 03:13:35 GMT

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.

Re: Summary and Ranking

stribor45 — Thu, 01 Feb 2024 18:25:03 GMT

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

Re: Summary and Ranking

Anonymous — Fri, 02 Feb 2024 01:52:40 GMT

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.